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?
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);