postgresql - 提高 PostgreSQL 查询的执行速度

Question

我在使用以下查询时遇到严重的 PostgreSQL 效率问题。即使服务器中有足够的 CPU 和内存，执行速度也太低。

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

通过EXPLAIN ANALYSE得到的查询计划如下:

  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

我相信 NOT IN 子查询是这里的罪魁祸首，它给出了额外的 SubPlan 1。

目前kvm_core_voucherdet.vchrdet_bill_id上有一个btree索引。有没有什么方法可以通过添加额外的索引或其他机制来提高此查询的速度？

Answer 1

正如 Nick.McDermald 所说，您应该将 NOT IN 子句转换为

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)

这会让你加入，这可能会更快。

此外，您应该在kvm_bills 上创建一个索引:

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

如果常量不总是 0 和 2，请改用以下索引:

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