含UNION ALL与WHERE子句的子查询查询计划优化问询

以下查询生成了非最优查询计划：

WITH
  targets as (
    select 'bike' vehicle, id, dealer_name FROM bikes WHERE frame_size = 52
    union all
    select 'car' vehicle, id, dealer_name FROM cars
    -- 实际场景中包含数十张表
  )
SELECT
  dealers.name dealer,
  targets.vehicle,
  targets.id
FROM
  dealers
  JOIN targets
    ON dealers.name = targets.dealer_name
WHERE dealers.id in (54,12,456,315,468)

连接条件未下推至bikes和cars表，即便两表都在dealer_name列上建有索引，查询计划仍对两表执行顺序扫描（Sequential Scans）：

Hash Join  (cost=21.53..4528.63 rows=545 width=41) (actual time=0.349..46.148 rows=551 loops=1)
  Hash Cond: (bikes.dealer_name = dealers.name)
  Buffers: shared hit=1095
  ->  Append  (cost=0.00..3959.24 rows=108483 width=41) (actual time=0.012..33.637 rows=108321 loops=1)
        Buffers: shared hit=1082
        ->  Seq Scan on bikes  (cost=0.00..1791.00 rows=8483 width=41) (actual time=0.011..9.304 rows=8321 loops=1)
              Filter: (frame_size = 52)
              Rows Removed by Filter: 91679
              Buffers: shared hit=541
        ->  Seq Scan on cars  (cost=0.00..1541.00 rows=100000 width=41) (actual time=0.012..15.126 rows=100000 loops=1)
              Buffers: shared hit=541
  ->  Hash  (cost=21.46..21.46 rows=5 width=5) (actual time=0.024..0.026 rows=5 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        Buffers: shared hit=13
        ->  Index Scan using dealers_pkey on dealers  (cost=0.28..21.46 rows=5 width=5) (actual time=0.011..0.021 rows=5 loops=1)
              Index Cond: (id = ANY ('{54,12,456,315,468}'::integer[]))
              Buffers: shared hit=13
Planning Time: 0.195 ms
Execution Time: 46.252 ms

若移除bikes子查询中的WHERE子句，查询计划会显著更优（使用索引扫描，执行时间大幅降低）：

Nested Loop  (cost=5.34..2120.38 rows=1004 width=41) (actual time=0.042..1.460 rows=997 loops=1)
  Buffers: shared hit=943
  ->  Index Scan using dealers_pkey on dealers  (cost=0.28..21.46 rows=5 width=5) (actual time=0.015..0.030 rows=5 loops=1)
        Index Cond: (id = ANY ('{54,12,456,315,468}'::integer[]))
        Buffers: shared hit=13
  ->  Append  (cost=5.07..417.78 rows=200 width=41) (actual time=0.023..0.260 rows=199 loops=5)
        Buffers: shared hit=930
        ->  Bitmap Heap Scan on bikes  (cost=5.07..208.39 rows=100 width=41) (actual time=0.021..0.119 rows=97 loops=5)
              Recheck Cond: (dealer_name = dealers.name)
              Heap Blocks: exact=450
              Buffers: shared hit=460
              ->  Bitmap Index Scan on bikes_dealer_name_idx  (cost=0.00..5.04 rows=100 width=0) (actual time=0.011..0.011 rows=97 loops=5)
                    Index Cond: (dealer_name = dealers.name)
                    Buffers: shared hit=10
        ->  Bitmap Heap Scan on cars  (cost=5.07..208.39 rows=100 width=41) (actual time=0.019..0.121 rows=102 loops=5)
              Recheck Cond: (dealer_name = dealers.name)
              Heap Blocks: exact=460
              Buffers: shared hit=470
              ->  Bitmap Index Scan on cars_dealer_name_idx  (cost=0.00..5.04 rows=100 width=0) (actual time=0.009..0.009 rows=102 loops=5)
                    Index Cond: (dealer_name = dealers.name)
                    Buffers: shared hit=10
Planning Time: 0.236 ms
Execution Time: 1.533 ms

提问 #

能否强制将连接条件下推至子查询并强制使用索引？或有其他优化该查询性能的方法？

实际场景中targets CTE包含数十张表，不希望将查询改写为对每张目标表单独连接的形式。

测试数据生成SQL #

CREATE TABLE dealers AS
SELECT
  id,
  (SELECT string_agg(CHR(65+(random() * 25)::integer), '') FROM generate_series(1, 4) WHERE id>0) name
FROM generate_series(1, 1000) AS id
;
ALTER TABLE dealers ADD primary key (id);
CREATE INDEX ON dealers(name);


CREATE TABLE bikes AS
SELECT
  generate_series AS id,
  (SELECT name FROM dealers WHERE dealers.id = (SELECT (random()*1000)::int WHERE generate_series>0)) AS dealer_name,
  (random()*12+50)::int as frame_size
FROM generate_series(1, 100000);
ALTER TABLE bikes ADD primary key (id);
CREATE INDEX ON bikes(dealer_name);


CREATE TABLE cars AS
SELECT
  generate_series as id,
  (SELECT name FROM dealers WHERE dealers.id = (SELECT (random()*1000)::int WHERE generate_series>0)) AS dealer_name,
  (random()*7+14)::int as wheel_size
FROM generate_series(1, 100000);
ALTER TABLE cars ADD primary key (id);
CREATE INDEX ON cars(dealer_name);


ANALYZE;

1. 用子查询替代CTE（核心优化方案） #

PostgreSQL中CTE默认是优化屏障（optimization fence），查询优化器无法将外部连接条件下推至CTE内部。将CTE替换为子查询后，优化器可以将dealers.name = targets.dealer_name的条件下推到各个子表，从而利用dealer_name索引：

SELECT
  dealers.name dealer,
  targets.vehicle,
  targets.id
FROM
  dealers
  JOIN (
    select 'bike' vehicle, id, dealer_name FROM bikes WHERE frame_size = 52
    union all
    select 'car' vehicle, id, dealer_name FROM cars
    -- 其他表继续追加union all
  ) targets
    ON dealers.name = targets.dealer_name
WHERE dealers.id in (54,12,456,315,468)

该写法无需修改多表union all的结构，完全适配数十张表的场景，优化器会自动选择嵌套循环+索引扫描的高效计划。

2. 创建复合索引（针对带过滤条件的表） #

对于bikes这类带有额外过滤条件的表，创建复合索引可以让优化器同时利用过滤条件和连接条件，进一步提升检索效率：

CREATE INDEX bikes_frame_size_dealer_name_idx ON bikes(frame_size, dealer_name);

这个索引能直接定位到frame_size=52且dealer_name匹配指定值的记录，减少不必要的数据扫描。

3. 临时调整优化器参数（应急方案） #

如果必须保留CTE写法，可以临时禁用哈希连接，迫使优化器选择嵌套循环连接，触发条件下推：

SET enable_hashjoin = off;
-- 执行原CTE查询
SET enable_hashjoin = on; -- 执行完成后恢复默认值

注意：该方法属于临时 workaround，不建议长期使用，会影响其他查询的优化计划选择。

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