716
PostgreSQL索引走錯一例分析
生成數據
create table test(id1 int, id2 int, id3 int);
create index id1_idx on test using btree (id1);
create index id2_idx on test using btree (id2);
insert into test select t,t ,t from generate_series(10000000, 0, -1) as t;
insert into test select 10000001, 10000001 , (random()*100000)::int from generate_series(1, 5000);
analyze test;
測試SQL
explain analyze select min(id1) from test where id2 = 10000001;
上麵我們在列id1和id2分別創建了索引，我們的猜想是優化器會在id1_idx和id2_idx上選擇一個最優的執行計劃，But.... 請看下麵
現象
下麵是數據實際的執行計劃，可以看到使用了id1_idx索引，然而執行時間很長
postgres=> explain analyze select min(id1) from test where id2 = 10000001;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=103.28..103.29 rows=1 width=0) (actual time=9860.209..9860.209 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.43..103.28 rows=1 width=4) (actual time=9860.199..9860.202 rows=1 loops=1)
-> Index Scan using id1_idx on test (cost=0.43..445840.93 rows=4335 width=4) (actual time=9860.197..9860.197 rows=1 loops=1)
Index Cond: (id1 IS NOT NULL)
Filter: (id2 = 10000001)
Rows Removed by Filter: 10000001
Planning time: 99.912 ms
Execution time: 9860.282 ms
(9 rows)
Time: 10069.370 ms
我們換成id2_idx索引試試，改造一下SQL，min(id1)改成min(id1+0) 即可，結果令人驚訝，執行時間比使用id1_idx快很多。
postgres=> explain analyze select min(id1+0) from test where id2 = 10000001;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=295.38..295.39 rows=1 width=4) (actual time=1.878..1.878 rows=1 loops=1)
-> Index Scan using id2_idx on test (cost=0.43..273.70 rows=4335 width=4) (actual time=0.034..1.234 rows=5000 loops=1)
Index Cond: (id2 = 10000001)
Planning time: 0.126 ms
Execution time: 1.931 ms
(5 rows)
Time: 6.889 ms
分析及總結
執行計劃由代價決定的，我們先看使用id1_idx索引的代價(cost=103.28..103.29)，是怎麼計算出來的呢？id1_idx索引總代價445840.93，滿足條件記錄數是4335, 我們需要的是最小值，因此隻要找到索引第一條滿足條件的記錄即可，找到第一條記錄的代價=445840.93/4335 ~= 103.29(優化器的代價模型是假設這4336條記錄平均分布)，而走id2_idx索引的代價為273.70，根據計算走id1_idx代價確實很低，那麼是什麼原因導致執行起來很慢呢？通過我們生產的數據看，10000001這個記錄的數據分布集中在表的結尾，所以找到索引第一條記錄代價並不是445840.93/4335，而是接近445840.93。
PG優化器對這樣的case的優化並非完美，在生成執行計劃的過程中可以結合一下數據分布特點，不斷地優化代價模型。作為DBA，我們也要結合具體業務進行SQL優化，避免讓優化器誤判。
最後更新：2017-07-28 14:32:45