是什么讓糟糕的行估計成為 SQL 查詢性能的痛點？我很想知道內部原因。
通常，錯誤的行估計實際上會選擇正確的計劃，而良好查詢和錯誤查詢之間的唯一區別將是估計的行數。
為什么經常會出現如此巨大的性能差異？
是因為 Postgres 使用行估計來分配記憶體嗎？
uj5u.com熱心網友回復：
Postgresql優化器是一個基于成本的優化器（CBO），查詢會按照執行計劃中最小的成本來執行，成本會根據表的統計來計算。
為什么在 Postgres 中錯誤的行估計很慢？
因為錯誤的統計資料可能會選擇錯誤的執行計劃。這是一個例子
有兩個表，T1有 20000000 行，T2有 1000000 行。
CREATE TABLE T1 (
ID INT NOT NULL PRIMARY KEY,
val INT NOT NULL,
col1 UUID NOT NULL,
col2 UUID NOT NULL,
col3 UUID NOT NULL,
col4 UUID NOT NULL,
col5 UUID NOT NULL,
col6 UUID NOT NULL
);
INSERT INTO T1
SELECT i,
RANDOM() * 1000000,
md5(random()::text || clock_timestamp()::text)::uuid,
md5(random()::text || clock_timestamp()::text)::uuid,
md5(random()::text || clock_timestamp()::text)::uuid,
md5(random()::text || clock_timestamp()::text)::uuid,
md5(random()::text || clock_timestamp()::text)::uuid,
md5(random()::text || clock_timestamp()::text)::uuid
FROM generate_series(1,20000000) i;
CREATE TABLE T2 (
ID INT NOT NULL PRIMARY KEY,
val INT NOT NULL,
col1 UUID NOT NULL,
col2 UUID NOT NULL,
col3 UUID NOT NULL,
col4 UUID NOT NULL,
col5 UUID NOT NULL,
col6 UUID NOT NULL
);
INSERT INTO T2
SELECT i,
RANDOM() * 1000000,
md5(random()::text || clock_timestamp()::text)::uuid,
md5(random()::text || clock_timestamp()::text)::uuid,
md5(random()::text || clock_timestamp()::text)::uuid,
md5(random()::text || clock_timestamp()::text)::uuid,
md5(random()::text || clock_timestamp()::text)::uuid,
md5(random()::text || clock_timestamp()::text)::uuid
FROM generate_series(1,1000000) i;
當我們join在表上做時，我們會得到一個可能使用的執行計劃Merge JOIN
EXPLAIN (ANALYZE,TIMING ON,BUFFERS ON)
SELECT t1.*
FROM T1
INNER JOIN T2 ON t1.id = t2.id
WHERE t1.id < 1000000
"Gather (cost=1016.37..30569.85 rows=53968 width=104) (actual time=0.278..837.297 rows=999999 loops=1)"
" Workers Planned: 2"
" Workers Launched: 2"
" Buffers: shared hit=38273 read=21841"
" -> Merge Join (cost=16.37..24173.05 rows=22487 width=104) (actual time=11.993..662.770 rows=333333 loops=3)"
" Merge Cond: (t2.id = t1.id)"
" Buffers: shared hit=38273 read=21841"
" -> Parallel Index Only Scan using t2_pkey on t2 (cost=0.42..20147.09 rows=416667 width=4) (actual time=0.041..69.947 rows=333333 loops=3)"
" Heap Fetches: 0"
" Buffers: shared hit=6 read=2732"
" -> Index Scan using t1_pkey on t1 (cost=0.44..48427.24 rows=1079360 width=104) (actual time=0.041..329.874 rows=999819 loops=3)"
" Index Cond: (id < 1000000)"
" Buffers: shared hit=38267 read=19109"
"Planning:"
" Buffers: shared hit=4 read=8"
"Planning Time: 0.228 ms"
"Execution Time: 906.760 ms"
但是當我如下更新很多行時，100000000當 id 小于時，讓 id 加上1000000
update T1
set id = id 100000000
where id < 1000000
我們再次使用相同的查詢，它將使用Merge JOIN，但應該有另一個更好的選擇而不是Merge JOIN。
如果您沒有達到 autovacuum_analyze_threshold （autovacuum_analyze_threshold默認值0.1意味著我們需要創建多個10%死元組 postgresql 將自動更新統計資訊）
EXPLAIN (ANALYZE,TIMING ON,BUFFERS ON)
SELECT t1.*
FROM T1
INNER JOIN T2 ON t1.id = t2.id
WHERE t1.id < 1000000
"Gather (cost=1016.37..30707.83 rows=53968 width=104) (actual time=51.403..55.517 rows=0 loops=1)"
" Workers Planned: 2"
" Workers Launched: 2"
" Buffers: shared hit=8215"
" -> Merge Join (cost=16.37..24311.03 rows=22487 width=104) (actual time=6.736..6.738 rows=0 loops=3)"
" Merge Cond: (t2.id = t1.id)"
" Buffers: shared hit=8215"
" -> Parallel Index Only Scan using t2_pkey on t2 (cost=0.42..20147.09 rows=416667 width=4) (actual time=0.024..0.024 rows=1 loops=3)"
" Heap Fetches: 0"
" Buffers: shared hit=8"
" -> Index Scan using t1_pkey on t1 (cost=0.44..50848.71 rows=1133330 width=104) (actual time=6.710..6.710 rows=0 loops=3)"
" Index Cond: (id < 1000000)"
" Buffers: shared hit=8207"
"Planning:"
" Buffers: shared hit=2745"
"Planning Time: 3.938 ms"
"Execution Time: 55.550 ms"
當我們使用手動ANALYZE T1;表示更新T1表統計資訊時，再次查詢會得到Nested Loop比Merge JOIN
"QUERY PLAN"
"Nested Loop (cost=0.86..8.90 rows=1 width=104) (actual time=0.004..0.004 rows=0 loops=1)"
" Buffers: shared hit=3"
" -> Index Scan using t1_pkey on t1 (cost=0.44..4.46 rows=1 width=104) (actual time=0.003..0.003 rows=0 loops=1)"
" Index Cond: (id < 1000000)"
" Buffers: shared hit=3"
" -> Index Only Scan using t2_pkey on t2 (cost=0.42..4.44 rows=1 width=4) (never executed)"
" Index Cond: (id = t1.id)"
" Heap Fetches: 0"
"Planning:"
" Buffers: shared hit=20"
"Planning Time: 0.232 ms"
"Execution Time: 0.027 ms"
小結論：
表中的精確統計資訊將幫助優化器通過精確的 COST 從表中獲得正確的執行計劃。
這是一個幫助我們搜索last_analyze和last_vacuum最后一次的腳本。
SELECT
schemaname, relname,
last_vacuum, last_autovacuum,
vacuum_count, autovacuum_count,
last_analyze,last_autoanalyze
FROM pg_stat_user_tables
where relname = 'tablename';
uj5u.com熱心網友回復：
行數估計值用于計算不同計劃的成本。當這些估計是這樣時，計劃的最終成本將意味著它最終使用了錯誤的計劃。例如掃描一個表，因為它認為它需要表的重要部分，而實際上只需要幾行，使用索引可以更快地檢索這些行。
uj5u.com熱心網友回復：
行數估計會影響優化器的進一步決策，因此錯誤的估計會導致錯誤的計劃。
以我的經驗，問題通常發生在決定正確加入策略的程序中：
當行數被低估時，PostgreSQL 可能會選擇嵌套回圈連接而不是散列或合并連接，但最終內部表的掃描頻率比 PostgreSLQ 認為的要高，從而導致性能下降。
相反，如果 PostgreSQL 高估了行數，它可能會選擇一個散列或合并連接并完全掃描兩個表，這可能比內表上的幾個索引掃描慢得多。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/436580.html