我有一个包含 ~6.9m 行的主要事实表 t1,一个包含 1329 行的第二维度表 t2,以及一个包含 ~6.9m 行的第三个表 t3(1-1 与 t1,分开,因为它是由不同的批处理过程生成的)。
当我使用 t2 中的某些内容和ORDER BY
t3 中的某些内容进行过滤LIMIT 500
时,PostgreSQL 14.5 会选择一个嵌套循环计划。t2 上的筛选器匹配 t2 中大约 24% 的行,但在 t1 中,所有这些值仅出现在 690 万行中的 10 行中。
我相信规划器假设如果我的条件匹配 t2 中 24% 的行,它也会匹配 t1 中 24% 的行,因此它可以快速向下扫描 t3 上的 ORDER BY 索引并在 500 行后停止。相反,它最终会在几分钟内扫描几乎整个表格。
我已经尝试将 t1.t2_id 的统计目标提高到 1000,这超过了唯一值的数量 (559)。t2 的统计数据很好:它认为我的过滤器将匹配 t2 结果的 0.2437923,它确实匹配(1329 行中的 324 行)。问题是它认为这意味着它也将匹配 t1 结果的 0.2437923,而且还差得远。有一个步骤认为它会匹配 t1 的 1682925/6903110 行(又是 0.2437923),但实际上只有 10 行。
有没有办法让它更多地了解 t2_id 列的分布,因为它适用于连接 - 它在 t1 和 t2 之间有很大的不同?
explain analyze SELECT t1.col1, t3.t3_value, t1.t2_id, t2..., t1....
FROM t1
INNER JOIN t2 ON t1.t2_id = t2.t2_id
INNER JOIN t3 ON t1.t1_id = t3.t1_id
WHERE ((t1.some_date >= '2022-01-01') AND (t1.some_date <= '2022-12-31')) AND (t2.somearray @> '{"Some value"}'::text[])
ORDER BY t3.t3_value DESC NULLS LAST
LIMIT 500;
Limit (cost=1.15..1393.95 rows=500 width=168) (actual time=252.891..241643.196 rows=10 loops=1)
-> Nested Loop (cost=1.15..4687957.28 rows=1682925 width=168) (actual time=252.889..241643.186 rows=10 loops=1)
-> Nested Loop (cost=0.86..4515363.25 rows=6903110 width=142) (actual time=0.546..238786.757 rows=6905136 loops=1)
-> Index Scan Backward using t3_t3_value_idx on t39b4 t3 (cost=0.43..384965.62 rows=6905138 width=10) (actual time=0.419..86244.771 rows=6905136 loops=1)
-> Index Scan using t1_id on t1 t1 (cost=0.43..0.60 rows=1 width=140) (actual time=0.022..0.022 rows=1 loops=6905136)
Index Cond: (t1_id = t3.t1_id)
Filter: ((some_date >= '2022-01-01'::date) AND (some_date <= '2022-12-31'::date))
-> Memoize (cost=0.29..0.31 rows=1 width=33) (actual time=0.000..0.000 rows=0 loops=6905136)
Cache Key: t1.t2_id
Cache Mode: logical
Hits: 6904577 Misses: 559 Evictions: 0 Overflows: 0 Memory Usage: 40kB
-> Index Scan using t2_id on t2 t2 (cost=0.28..0.30 rows=1 width=33) (actual time=0.015..0.015 rows=0 loops=559)
Index Cond: (t2_id = t1.t2_id)
Filter: (somearray @> '{Some value}'::text[])
Rows Removed by Filter: 1
Planning Time: 2.817 ms
Execution Time: 241643.279 ms
我可以使用 获得不错的性能set enable_nestloop = off
,但是当 Nested Loops 工作时,它有时会快 100 倍……问题是它有时也会慢 100 倍。
相关问题:
- 当存在 LIMIT 时,Postgres 12.1 使用 Index Only Scan Backward 而不是索引
- Postgres 有时对 WHERE a IN (...) ORDER BY b LIMIT N 使用劣质索引
作为参考,以下是计划enable_nestloop = off
:
Limit (cost=491707.51..491765.85 rows=500 width=168) (actual time=2272.171..2299.444 rows=10 loops=1)
-> Gather Merge (cost=491707.51..655369.37 rows=1402718 width=168) (actual time=2249.844..2277.115 rows=10 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=490707.49..492460.89 rows=701359 width=168) (actual time=2220.992..2220.999 rows=3 loops=3)
Sort Key: t3.t3_value DESC NULLS LAST
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 26kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Parallel Hash Join (cost=304205.51..455759.53 rows=701359 width=168) (actual time=2066.257..2220.918 rows=3 loops=3)
Hash Cond: (t3.t1_id = t1.t1_id)
-> Parallel Seq Scan on t3 t3 (cost=0.00..93305.41 rows=2877141 width=10) (actual time=0.195..431.625 rows=2301712 loops=3)
-> Parallel Hash (cost=278999.52..278999.52 rows=701359 width=166) (actual time=1203.337..1203.341 rows=3 loops=3)
Buckets: 131072 Batches: 32 Memory Usage: 1056kB
-> Hash Join (cost=66.61..278999.52 rows=701359 width=166) (actual time=1188.304..1202.565 rows=3 loops=3)
Hash Cond: (t1.t2_id = t2.t2_id)
-> Parallel Seq Scan on t1 t1 (cost=0.00..271357.04 rows=2876870 width=140) (actual time=11.000..987.052 rows=2301712 loops=3)
Filter: ((somedate >= '2022-01-01'::date) AND (somedate <= '2022-12-31'::date))
-> Hash (cost=62.56..62.56 rows=324 width=33) (actual time=0.292..0.294 rows=324 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 31kB
-> Bitmap Heap Scan on t2 (cost=10.51..62.56 rows=324 width=33) (actual time=0.087..0.233 rows=324 loops=3)
Recheck Cond: (somearray @> '{Some value}'::text[])
Heap Blocks: exact=18
-> Bitmap Index Scan on t2_somearray_idx (cost=0.00..10.43 rows=324 width=0) (actual time=0.054..0.055 rows=324 loops=3)
Index Cond: (somearray @> '{Some value}'::text[])
Execution Time: 2301.592 ms