我有以下查询：
EXPLAIN ANALYZE
WITH up AS (
SELECT
ua1.id ua1_id,
gp1.id gp1_id,
upai1.id upai1_id,
upbi1.id upbi1_id,
upiv1.id upiv1_id,
vi.id vi_id,
c.id c_id,
sua.id sua_id
FROM ua1
LEFT JOIN gp1 ON
ua1.gp1_id = gp1.id
LEFT JOIN upai1 ON
upai1.id = ua1.upai1_id
LEFT JOIN upbi1 ON
upbi1.id = ua1.upbi1_id
LEFT JOIN upiv1 ON
upiv1.id = ua1.upiv1_id
LEFT JOIN vi ON
vi.id = ua1.vi_id
LEFT JOIN c ON
c.id = vi.c_id
LEFT JOIN sua ON
sua.ua_id = ua1.id
)
SELECT
up1.*,
hrm1.ua_id,
hrm1.hr_id
FROM hrm1 hrm1
INNER JOIN up up1 ON
up1.ua1_id = hrm1.ua_id
WHERE
hrm1.hr_id = 1
从而产生以下查询计划：
Gather (cost=42072.86..122816.40 rows=6 width=40) (actual time=7.207..10.132 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Join (cost=41072.86..121815.80 rows=2 width=40) (actual time=0.068..0.070 rows=0 loops=3)
Hash Cond: (ua1.id = hrm1.ua_id)
-> Parallel Hash Left Join (cost=41045.41..121231.60 rows=212096 width=32) (never executed)
Hash Cond: (ua1.id = sua.ua_id)
-> Hash Left Join (cost=36200.82..115830.26 rows=212096 width=28) (never executed)
Hash Cond: (vi.c_id = c.id)
-> Parallel Hash Left Join (cost=36192.93..115254.76 rows=212096 width=28) (never executed)
Hash Cond: (ua1.vi_id = vi.id)
-> Hash Left Join (cost=11544.90..85850.97 rows=212096 width=24) (never executed)
Hash Cond: (ua1.upiv1_id = upiv1.id)
-> Hash Left Join (cost=11506.32..85255.64 rows=212096 width=24) (never executed)
Hash Cond: (ua1.upbi1_id = upbi1.id)
-> Parallel Hash Left Join (cost=11216.81..84409.38 rows=212096 width=24) (never executed)
Hash Cond: (ua1.upai1_id = upai1.id)
-> Hash Left Join (cost=677.14..70177.96 rows=212096 width=24) (never executed)
Hash Cond: (ua1.location_gp1_id = gp1.id)
-> Parallel Seq Scan on ua ua1 (cost=0.00..68943.96 rows=212096 width=24) (never executed)
-> Hash (cost=478.69..478.69 rows=15876 width=4) (never executed)
-> Index Only Scan using gp1_pkey on gp1 gp1 (cost=0.29..478.69 rows=15876 width=4) (never executed)
Heap Fetches: 0
-> Parallel Hash (cost=7815.85..7815.85 rows=165985 width=4) (never executed)
-> Parallel Seq Scan on upai1 upai1 (cost=0.00..7815.85 rows=165985 width=4) (never executed)
-> Hash (cost=170.34..170.34 rows=9534 width=4) (never executed)
-> Seq Scan on upbi1 upbi1 (cost=0.00..170.34 rows=9534 width=4) (never executed)
-> Hash (cost=22.70..22.70 rows=1270 width=4) (never executed)
-> Seq Scan on upiv1 upiv1 (cost=0.00..22.70 rows=1270 width=4) (never executed)
-> Parallel Hash (cost=17455.57..17455.57 rows=438357 width=8) (never executed)
-> Parallel Seq Scan on vi vi (cost=0.00..17455.57 rows=438357 width=8) (never executed)
-> Hash (cost=5.17..5.17 rows=217 width=4) (never executed)
-> Seq Scan on c c (cost=0.00..5.17 rows=217 width=4) (never executed)
-> Parallel Hash (cost=4679.82..4679.82 rows=13182 width=8) (never executed)
-> Parallel Seq Scan on sua sua (cost=0.00..4679.82 rows=13182 width=8) (never executed)
-> Hash (cost=27.37..27.37 rows=6 width=8) (actual time=0.021..0.021 rows=0 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Index Scan using hrm1_hr_id_idx on hrm1 hrm1 (cost=0.42..27.37 rows=6 width=8) (actual time=0.020..0.020 rows=0 loops=3)
Index Cond: (hr_id = 1)
Planning Time: 5.125 ms
Execution Time: 10.223 ms
然而，删除CTE内部的一个或多个联接(哪一个似乎无关紧要(会导致计划器退回到执行嵌套循环联接，这要快得多。注意：在这个简化的例子中，差异并不大，但在最终查询中使用嵌套循环连接而不是哈希连接，这是不到一毫秒的时间和超过一秒的时间之间的差异。
Nested Loop Left Join (cost=2.83..91.77 rows=6 width=36) (actual time=0.016..0.017 rows=0 loops=1)
-> Nested Loop Left Join (cost=2.42..88.95 rows=6 width=32) (actual time=0.015..0.016 rows=0 loops=1)
-> Nested Loop Left Join (cost=1.99..85.80 rows=6 width=32) (actual time=0.015..0.016 rows=0 loops=1)
-> Nested Loop Left Join (cost=1.83..84.76 rows=6 width=32) (actual time=0.015..0.016 rows=0 loops=1)
-> Nested Loop Left Join (cost=1.55..82.95 rows=6 width=32) (actual time=0.015..0.016 rows=0 loops=1)
-> Nested Loop Left Join (cost=1.13..79.83 rows=6 width=32) (actual time=0.015..0.016 rows=0 loops=1)
-> Nested Loop (cost=0.84..78.01 rows=6 width=32) (actual time=0.015..0.016 rows=0 loops=1)
-> Index Scan using hrm1_hr_id_idx on hrm1 hrm1 (cost=0.42..27.37 rows=6 width=8) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: (hr_id = 6766566)
-> Index Scan using ua_pkey on ua ua1 (cost=0.42..8.44 rows=1 width=24) (never executed)
Index Cond: (id = hrm1.ua_id)
-> Index Only Scan using gp1_pkey on gp1 gp1 (cost=0.29..0.30 rows=1 width=4) (never executed)
Index Cond: (id = ua1.location_gp1_id)
Heap Fetches: 0
-> Index Only Scan using upai1_pkey on upai1 upai1 (cost=0.42..0.52 rows=1 width=4) (never executed)
Index Cond: (id = ua1.upai1_id)
Heap Fetches: 0
-> Index Only Scan using upbi1_pkey on upbi1 upbi1 (cost=0.29..0.30 rows=1 width=4) (never executed)
Index Cond: (id = ua1.upbi1_id)
Heap Fetches: 0
-> Index Only Scan using upiv1_pkey on upiv1 upiv1 (cost=0.15..0.17 rows=1 width=4) (never executed)
Index Cond: (id = ua1.upiv1_id)
Heap Fetches: 0
-> Index Only Scan using vi_id_pkey on vi vi (cost=0.43..0.52 rows=1 width=4) (never executed)
Index Cond: (id = ua1.vi_id)
Heap Fetches: 0
-> Index Scan using sua_ua_id_uniq_idx on sua sua (cost=0.41..0.47 rows=1 width=8) (never executed)
Index Cond: (ua_id = ua1.id)
Planning Time: 7.002 ms
Execution Time: 0.089 ms
类似地，消除CTE并使用简化的SELECT也产生了具有嵌套循环联接的更优化的计划。
我想了解为什么再添加一个联接会导致查询规划器选择一个效率较低的计划，以及我们可以采取哪些步骤(比如强制执行一个约定，比如不使用CTE(来防止我们在查询发展和增长时再次遇到同样的问题。
看起来您已经用完了join_collapse_limit(默认情况下为8(，Postgres为了抵御如雨后春笋般增长的计划成本，停止了重新排序联接以优化查询计划的尝试。
"；但是CTE只连接8个表"> 好吧，假设至少有Postgres12，那么在主查询中可以内联普通的CTE。这正是我在这里看到的，这导致了9张表。繁荣 参见： 为什么搜索词的微小变化会大大减慢查询速度？ 在WHERE子句中多次使用同一列 Postgresql join_collapse_limit和查询计划的时间 你可以尝试增加这个限制，但要注意按因素(！(增长的组合，从而增加规划成本。请注意这里已经占主导地位的查询计划成本： 对于重复执行，准备好的语句或PL/pgSQL函数以类似的方式保存查询计划可能会有很大帮助。。。 或者，您强制使用 参见： 缺点：增加了无需实现中间结果的成本。 或者手动重新排序联接以优化查询计划。(这可能会随着底层数据库基数的变化而发生比特腐烂…(可能的解决方案
Planning Time: 7.002 ms
Execution Time: 0.089 ms
MATERIALIZED关键字来具体化CTE，从而也强制使用执行单独的查询计划WITH up AS MATERIALIZED ( ...