##
一个SQL优化案例，看懂PostgreSQL优化器有多聪明
1、案例分析
bitnami_odoo=> explain analyze SELECT "sale_order".id FROM "sale_order" WHERE (("sale_order"."active" = true) AND (("sale_order"."publish_loan_to_rmq_create" = true) OR ("sale_order"."publish_loan_to_rmq_update" = true))) ORDER BY "sale_order"."date_order" DESC,"sale_order"."id" DESC LIMIT 80;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=306875.67..306875.68 rows=1 width=12) (actual time=747.878..781.536 rows=5 loops=1)
-> Sort (cost=306875.67..306875.68 rows=1 width=12) (actual time=747.876..781.533 rows=5 loops=1)
Sort Key: date_order DESC, id DESC
Sort Method: quicksort Memory: 25kB
-> Gather (cost=1000.00..306875.66 rows=1 width=12) (actual time=747.816..781.520 rows=5 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Seq Scan on sale_order (cost=0.00..305875.57 rows=1 width=12) (actual time=743.835..743.840 rows=1 loops=5)
Filter: (active AND (publish_loan_to_rmq_create OR publish_loan_to_rmq_update))
Rows Removed by Filter: 1739119
Planning Time: 0.290 ms
Execution Time: 781.559 ms
(12 rows)
从你的 EXPLAIN ANALYZE 可以看到 查询花了 781ms 且扫描了近 174 万行，说明这条 SQL 的瓶颈在于：
🔥 缺少能支持筛选条件和排序条件的合适索引。
2、解决方案
优化方案1：创建索引
CREATE INDEX idx_sale_order_active_publish_order_id
ON sale_order (date_order DESC, id DESC)
WHERE active = true AND (publish_loan_to_rmq_create = true OR publish_loan_to_rmq_update = true);
然而并不选择…
优化方案2：尝试改写OR
改写方案1：
WITH orders AS (
SELECT id, date_order
FROM sale_order
WHERE active = true AND publish_loan_to_rmq_create = true
UNION ALL
SELECT id, date_order
FROM sale_order
WHERE active = true AND publish_loan_to_rmq_update = true
)
SELECT id
FROM orders
ORDER BY date_order DESC, id DESC
LIMIT 80;
改写方案2：
SELECT id
FROM (
SELECT id, date_order
FROM sale_order
WHERE active = true AND publish_loan_to_rmq_create = true
UNION ALL
SELECT id, date_order
FROM sale_order
WHERE active = true AND publish_loan_to_rmq_update = true
) AS orders
ORDER BY date_order DESC, id DESC
LIMIT 80;
检查执行计划之后，发现还是不适用索引。
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=612743.18..612743.19 rows=2 width=12) (actual time=1159.439..1210.410 rows=5 loops=1)
-> Sort (cost=612743.18..612743.19 rows=2 width=12) (actual time=1159.437..1210.407 rows=5 loops=1)
Sort Key: sale_order.date_order DESC, sale_order.id DESC
Sort Method: quicksort Memory: 25kB
-> Gather (cost=1000.00..612743.17 rows=2 width=12) (actual time=973.495..1210.395 rows=5 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Append (cost=0.00..611742.97 rows=2 width=12) (actual time=1119.017..1156.187 rows=1 loops=5)
-> Parallel Seq Scan on sale_order (cost=0.00..305871.48 rows=1 width=12) (actual time=573.718..573.718 rows=0 loops=5)
Filter: (active AND publish_loan_to_rmq_create)
Rows Removed by Filter: 1739120
-> Parallel Seq Scan on sale_order sale_order_1 (cost=0.00..305871.48 rows=1 width=12) (actual time=970.770..970.776 rows=2 loops=3)
Filter: (active AND publish_loan_to_rmq_update)
Rows Removed by Filter: 2898531
Planning Time: 0.188 ms
Execution Time: 1210.439 ms
(16 rows)
3、问题根源
从你的 EXPLAIN ANALYZE 看，优化器做了两个并行顺序扫描：
sql> Parallel Seq Scan on sale_order
Filter: (active AND publish_loan_to_rmq_create)
-> Parallel Seq Scan on sale_order sale_order_1
Filter: (active AND publish_loan_to_rmq_update)
这说明：
- PostgreSQL 没有使用你创建的索引。
- 原因可能是你创建的复合索引不完整，或者顺序不合适。
4、解决思路
4.1、 建立合适的多列联合索引
因为你的查询有多个过滤条件和排序字段：
sqlWHERE active = true AND publish_loan_to_rmq_create = true
ORDER BY date_order DESC, id DESC
你需要复合索引（多列组合索引）来覆盖查询，顺序很关键：
sql-- 为 create 条件建索引
CREATE INDEX CONCURRENTLY idx_sale_order_create_active_date_id
ON sale_order (active, publish_loan_to_rmq_create, date_order DESC, id DESC);
-- 为 update 条件建索引
CREATE INDEX CONCURRENTLY idx_sale_order_update_active_date_id
ON sale_order (active, publish_loan_to_rmq_update, date_order DESC, id DESC);
⚠️ 注意使用
CONCURRENTLY创建索引避免锁表（仅限生产环境），你可以先用CREATE INDEX测试效果。
4.2、检查改写结果
bitnami_odoo=> EXPLAIN (ANALYZE, BUFFERS)
bitnami_odoo-> SELECT id
bitnami_odoo-> FROM (
bitnami_odoo(> SELECT id, date_order
bitnami_odoo(> FROM sale_order
bitnami_odoo(> WHERE active = true AND publish_loan_to_rmq_create = true
bitnami_odoo(> UNION ALL
bitnami_odoo(> SELECT id, date_order
bitnami_odoo(> FROM sale_order
bitnami_odoo(> WHERE active = true AND publish_loan_to_rmq_update = true
bitnami_odoo(> ) AS orders
bitnami_odoo-> ORDER BY date_order DESC, id DESC
bitnami_odoo-> LIMIT 80;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=5.58..5.58 rows=2 width=12) (actual time=0.041..0.042 rows=5 loops=1)
Buffers: shared hit=13
-> Sort (cost=5.58..5.58 rows=2 width=12) (actual time=0.040..0.041 rows=5 loops=1)
Sort Key: sale_order.date_order DESC, sale_order.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=13
-> Append (cost=0.56..5.57 rows=2 width=12) (actual time=0.027..0.035 rows=5 loops=1)
Buffers: shared hit=13
-> Index Only Scan using idx_sale_order_create_active_date_id on sale_order (cost=0.56..2.78 rows=1 width=12) (actual time=0.011..0.012 rows=0 loops=1)
Index Cond: ((active = true) AND (publish_loan_to_rmq_create = true))
Heap Fetches: 0
Buffers: shared hit=4
-> Index Only Scan using idx_sale_order_update_active_date_id on sale_order sale_order_1 (cost=0.56..2.78 rows=1 width=12) (actual time=0.015..0.022 rows=5 loops=1)
Index Cond: ((active = true) AND (publish_loan_to_rmq_update = true))
Heap Fetches: 6
Buffers: shared hit=9
Planning Time: 0.211 ms
Execution Time: 0.058 ms
(18 rows)
这里是 Index Only
4.3、在不改写的情况下
bitnami_odoo=> explain analyze SELECT "sale_order".id FROM "sale_order"
bitnami_odoo-> WHERE (("sale_order"."active" = true) AND (("sale_order"."publish_loan_to_rmq_create" = true) OR ("sale_order"."publish_loan_to_rmq_update" = true)))
bitnami_odoo-> ORDER BY "sale_order"."date_order" DESC,"sale_order"."id" DESC LIMIT 80;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=5.57..5.58 rows=2 width=12) (actual time=0.048..0.051 rows=5 loops=1)
-> Sort (cost=5.57..5.58 rows=2 width=12) (actual time=0.048..0.049 rows=5 loops=1)
Sort Key: date_order DESC, id DESC
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on sale_order (cost=3.34..5.56 rows=2 width=12) (actual time=0.034..0.040 rows=5 loops=1)
Recheck Cond: ((active AND publish_loan_to_rmq_create) OR (active AND publish_loan_to_rmq_update))
Heap Blocks: exact=3
-> BitmapOr (cost=3.34..3.34 rows=2 width=0) (actual time=0.026..0.027 rows=0 loops=1)
-> Bitmap Index Scan on idx_sale_order_create_active_date_id (cost=0.00..1.67 rows=1 width=0) (actual time=0.013..0.013 rows=0 loops=1)
Index Cond: ((active = true) AND (publish_loan_to_rmq_create = true))
-> Bitmap Index Scan on idx_sale_order_update_active_date_id (cost=0.00..1.67 rows=1 width=0) (actual time=0.013..0.013 rows=6 loops=1)
Index Cond: ((active = true) AND (publish_loan_to_rmq_update = true))
Planning Time: 0.168 ms
Execution Time: 0.067 ms
(14 rows)
这里是Bitmap Index Scan
5、总结
尽管改写之后效率更高，但改写就要改代码，成本更高。所以这里选择成本最低的保留原有SQL语句，仅创建索引。
PS:PG的查询优化器确实有点意思
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
