Postgresql
PostgreSQL。不穩定的執行時間大大超過了計劃時間
PG版本:10.9
架構:
create table core_bundle ( id serial not null constraint core_bundle_pkey primary key, dt_add timestamp with time zone not null, dt_upd timestamp with time zone not null, aid integer not null constraint core_bundle_aid_check check (aid >= 0), prc_after timestamp with time zone, prc_grp uuid, status integer not null constraint core_bundle_status_check check (status >= 0) ); create index core_bundle_aid_2d875f86 on core_bundle (aid); create index core_bundle_prc_grp_a0b838ca on core_bundle (prc_grp); create index core_bundle_status_5cfd1324 on core_bundle (status);
以下查詢的執行時間有時會發生巨大變化。
explain analyze SELECT "core_bundle"."id", "core_bundle"."aid" FROM "core_bundle" WHERE ( "core_bundle"."prc_after" <= '2019-07-11T14:29:11.159787'::timestamp AND "core_bundle"."status" = 13 ) ORDER BY "core_bundle"."id" ASC LIMIT 1;
不錯的案例:
Limit (cost=266.19..266.19 rows=1 width=8) (actual time=0.288..0.289 rows=1 loops=1) -> Sort (cost=266.19..266.74 rows=221 width=8) (actual time=0.287..0.287 rows=1 loops=1) Sort Key: id Sort Method: top-N heapsort Memory: 25kB -> Index Scan using core_bundle_status_5cfd1324 on core_bundle (cost=0.43..265.08 rows=221 width=8) (actual time=0.029..0.257 rows=167 loops=1) Index Cond: (status = 13) Filter: (prc_after <= '2019-07-11 14:29:11.159787'::timestamp without time zone) Planning time: 0.138 ms Execution time: 0.319 ms
壞情況:
Limit (cost=0.43..127.12 rows=1 width=530) (actual time=2208.373..2208.375 rows=1 loops=1) -> Index Scan using core_bundle_pkey on core_bundle (cost=0.43..391587.09 rows=3091 width=530) (actual time=2208.372..2208.372 rows=1 loops=1) Filter: ((prc_after <= '2019-07-11 14:29:11.159787'::timestamp without time zone) AND (status = 13)) Rows Removed by Filter: 4179221 Planning time: 0.218 ms Execution time: 2208.419 ms
好的情況下共享命中率很低(大約 67),而壞情況下的共享命中率約為 1030528。
在糟糕的情況下,既沒有排序也沒有
status
索引。我想知道這背後的原因是什麼,以及可以做些什麼來擺脫不良案例?不幸的是,將索引固定
status
到某個值並不是一個真正的選擇,因為值在where
子句中有所不同。在這裡調整可能
default_statistics_target
會有所幫助,那麼我應該更喜歡什麼值?或者一個索引prc_after
會是有效的並且足以改善事情嗎?也許相關:
問題必須來自不同的統計數據;也許表格變化很大。
當 PostgreSQL 認為有這麼多行滿足條件,即
ORDER BY
使用主鍵索引按順序掃描行直到遇到第一個匹配項會更便宜時,就會出現糟糕的計劃。要麼這個假設是錯誤的,要麼這些條件之間的相關性是不幸的。無論如何,如果你知道第一個計劃總是正確的,你可以通過修改這樣的
ORDER BY
子句來告訴 PostgreSQL 不要使用主鍵索引:ORDER BY core_bundle.id + 0