禁用嵌套循环后PostgreSQL查询性能提升的原因及优化方案

问题背景 #

products表数据量超1000万，执行关联product_categories的查询时，筛选不存在的分类pc.category_id='asf'并按p.updated_at DESC排序，耗时超35秒；移除ORDER BY后查询速度正常。执行计划显示采用Nested Loop，会遍历全量products数据。设置SET enable_nestloop = off后查询恢复正常，但需要更合理的替代优化方案。

查询语句 #

explain analyze SELECT p.* FROM products p
INNER JOIN product_categories pc ON p.id = pc.product_id
WHERE pc.category_id = 'asf'
ORDER BY p.updated_at DESC LIMIT 21;

执行计划 #

Limit  (cost=1001.02..26849.88 rows=21 width=240) (actual time=35099.387..35396.240 rows=0 loops=1)
  ->  Gather Merge  (cost=1001.02..6249039.48 rows=5076 width=240) (actual time=35099.385..35396.237 rows=0 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Nested Loop  (cost=1.00..6247453.56 rows=2115 width=240) (actual time=35061.491..35061.492 rows=0 loops=3)
              ->  Parallel Index Scan Backward using idx_products_updated_at on products p  (cost=0.43..2596897.52 rows=5482982 width=240) (actual time=0.057..4123.748 rows=4563924 loops=3)
              ->  Index Scan using idx_product_categories_product_id on product_categories pc  (cost=0.56..0.66 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=13691771)
                    Index Cond: (product_id = p.id)
                    Filter: (category_id = 'asf'::text)
                    Rows Removed by Filter: 1
Planning Time: 1.157 ms
Execution Time: 35396.346 ms

问题原因 #

优化器选择了错误的执行顺序：优先按updated_at倒序扫描全量products，再逐个关联product_categories验证category_id='asf'。由于该分类不存在，导致遍历了1300多万次product_categories索引，最终耗时过长。而移除ORDER BY时，优化器会优先扫描product_categories过滤category_id，发现无匹配数据后直接返回，因此速度正常。

优化方案 #

1. 创建复合索引（最优方案） #

在product_categories表上创建(category_id, product_id)复合索引，让优化器能快速定位目标分类的product_id（即使分类不存在，也能瞬间确认无数据），避免全量扫描products：

CREATE INDEX idx_product_categories_category_product ON product_categories (category_id, product_id);

创建后，优化器会优先通过该索引过滤category_id='asf'，无匹配时直接返回空结果，查询耗时会大幅降低。

2. 强制查询执行顺序 #

通过子查询明确先过滤product_categories，再关联products，引导优化器选择更高效的执行路径：

SELECT p.*
FROM (
    SELECT product_id
    FROM product_categories
    WHERE category_id = 'asf'
) pc
JOIN products p ON p.id = pc.product_id
ORDER BY p.updated_at DESC LIMIT 21;

这种方式会先处理category_id过滤，若结果为空则直接结束，不会扫描products表。

3. 更新统计信息 #

若优化器选择错误计划是因为统计信息过时，更新表统计信息让优化器能准确判断行数：

ANALYZE product_categories;
ANALYZE products;

更新后，优化器会知晓category_id='asf'的行数为0，从而避免选择全量扫描products的计划。

4. 局部禁用嵌套循环（替代全局设置） #

不要全局禁用嵌套循环，可在单个查询会话中临时禁用，避免影响其他查询的计划选择：

SET LOCAL enable_nestloop = off;
SELECT p.* FROM products p
INNER JOIN product_categories pc ON p.id = pc.product_id
WHERE pc.category_id = 'asf'
ORDER BY p.updated_at DESC LIMIT 21;
RESET enable_nestloop;

内容的提问来源于stack exchange，提问作者Dubravko Petrovic