Increase execution speed of PostgreSQL query

Question

I am facing a serious PostgreSQL efficiency issue with the following query. The speed of execution is too low even with enough CPU and memory in the server.

SELECT "kvm_bills".*, "billcat"."cat_name", "contractor"."con_id", "contractor"."con_name", "contractor"."con_address", "contractor"."con_mobileno", "billbranch"."branch_name"
FROM "kvm_bills" LEFT JOIN
     "kvm_bill_categories" AS "billcat"
     ON billcat.cat_id =kvm_bills.bill_cat_id LEFT JOIN
     "kvm_bill_contractors" AS "contractor"
     ON contractor.con_id =kvm_bills.bill_con_id LEFT JOIN
     "kvm_core_branches" AS "billbranch"
     ON billbranch.branch_id =kvm_bills.bill_branch
WHERE (kvm_bills.deleted = 0) AND
      (bill_branch IN (258, 259, 332, 66, 65, 63, 168, 169, 170, 309, 330, 418, 257)) AND
      (kvm_bills.bill_id NOT IN (SELECT kvm_core_voucherdet.vchrdet_bill_id
                                 FROM kvm_core_voucherdet
                                 WHERE kvm_core_voucherdet.deleted=0
                                )
      ) AND
      (contractor.con_mobileno LIKE '123456') AND
      (bill_approve_stat = 2) AND
      (billcat.deleted = 0) AND
      (contractor.deleted = 0) AND
      (billbranch.deleted = 0) 
ORDER BY "bill_branch" DESC, "bill_ref_no" ASC

The query plan taken through EXPLAIN ANALYSE is as follows:

  QUERY PLAN
    Sort  (cost=501356982.86..501356982.86 rows=2 width=346) (actual time=155806.015..155806.015 rows=8 loops=1)
      Sort Key: kvm_bills.bill_branch, kvm_bills.bill_ref_no
      Sort Method: quicksort  Memory: 29kB
      ->  Nested Loop  (cost=0.00..501356982.85 rows=2 width=346) (actual time=2909.407..155805.861 rows=8 loops=1)
            ->  Nested Loop  (cost=0.00..501356982.26 rows=2 width=325) (actual time=2909.297..155805.599 rows=8 loops=1)
                  ->  Nested Loop  (cost=0.00..501356981.69 rows=2 width=310) (actual time=2909.073..155805.155 rows=8 loops=1)
                        Join Filter: (kvm_bills.bill_con_id = contractor.con_id)
                        Rows Removed by Join Filter: 7855
                        ->  Seq Scan on kvm_bills  (cost=0.00..501356587.87 rows=6446 width=228) (actual time=63.218..155799.854 rows=2621 loops=1)
                              Filter: ((deleted = 0) AND (bill_approve_stat = 2) AND (bill_branch = ANY ('{258,259,332,66,65,63,168,169,170,309,330,418,257}'::bigint[])) AND (NOT (SubPlan 1)))
                              Rows Removed by Filter: 271730
                              SubPlan 1
                                ->  Materialize  (cost=0.00..3442.08 rows=85093 width=8) (actual time=0.003..6.998 rows=50182 loops=11956)
                                      ->  Seq Scan on kvm_core_voucherdet  (cost=0.00..2683.61 rows=85093 width=8) (actual time=0.019..33.118 rows=84909 loops=1)
                                            Filter: (deleted = 0)
                                            Rows Removed by Filter: 6100
                        ->  Materialize  (cost=0.00..200.45 rows=2 width=82) (actual time=0.000..0.001 rows=3 loops=2621)
                              ->  Seq Scan on kvm_bill_contractors contractor  (cost=0.00..200.44 rows=2 width=82) (actual time=0.643..1.932 rows=3 loops=1)
                                    Filter: ((con_mobileno ~~ '123456'::text) AND (deleted = 0))
                                    Rows Removed by Filter: 5494
                  ->  Index Scan using kvm_bill_categories_pkey on kvm_bill_categories billcat  (cost=0.00..0.27 rows=1 width=23) (actual time=0.029..0.030 rows=1 loops=8)
                        Index Cond: (cat_id = kvm_bills.bill_cat_id)
                        Filter: (deleted = 0)
            ->  Index Scan using kvm_core_branches_pkey on kvm_core_branches billbranch  (cost=0.00..0.28 rows=1 width=29) (actual time=0.018..0.019 rows=1 loops=8)
                  Index Cond: (branch_id = kvm_bills.bill_branch)
                  Filter: (deleted = 0)
    Total runtime: 155807.130 ms

I believe that the NOT IN subquery is the culprit here which is giving the additional SubPlan 1.

Currently there is a btree index on kvm_core_voucherdet.vchrdet_bill_id. Is there any way the speed of this query can be improved either by adding additional indexes or by some other mechanism?

I always use NOT EXISTS over NOT IN. Do you know how to rewrite for that case? — Nick.Mc
– Nick.Mc, Commented May 8, 2019 at 10:49
For readability: add aliases for all tables in the query. And: use them for all referenced columns. — wildplasser
– wildplasser, Commented May 8, 2019 at 11:07
Also: some conditions in your WHILE clause will effectively reduce most (all?) of your LEFT JOINs to plain joins. — wildplasser
– wildplasser, Commented May 8, 2019 at 11:39

Laurenz Albe · Accepted Answer · 2019-05-08 11:03:46Z

4

Like Nick.McDermald says, you should convert the NOT IN clause to

NOT EXISTS (SELECT 1
            FROM kvm_core_voucherdet
            WHERE kvm_core_voucherdet.vchrdet_bill_id = kvm_bills.bill_id
              AND kvm_core_voucherdet.deleted = 0)

That will get you a join, which is probably faster.

In addition, you should create an index on kvm_bills:

CREATE INDEX ON kvm_bills (bill_branch)
   WHERE deleted = 0 AND bill_approve_stat = 2;

If the constants are not always 0 and 2, use the following index instead:

CREATE INDEX ON kvm_bills (bill_approve_stat, deleted, bill_branch);

answered May 8, 2019 at 11:03

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Increase execution speed of PostgreSQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related