限價查詢和按順序查詢執行速度太慢

我有下表：

CREATE TABLE dpg2
(
 account_id integer NOT NULL,
 tank_id integer NOT NULL,
 battles integer,
 dmg integer,
 frags integer,
 wins integer,
 recent_battles integer[],
 recent_dmg integer[],
 recent_frags integer[],
 recent_wins integer[],
 dpg real,
 recent_ts timestamp with time zone[],
 recent_dpg real,
 CONSTRAINT dpg2_pkey PRIMARY KEY (account_id, tank_id)
)

使用此索引：

CREATE INDEX dpg_tank_id_idx
 ON dpg2
 USING btree
 (tank_id, dpg DESC NULLS LAST);

我執行了以下查詢：

explain analyze
   select dpg2.account_id, tank_id, (select nickname from players2 where players2.account_id = dpg2.account_id), dpg2.battles, dpg,
               frags*1.0/dpg2.battles, wins*100.0/dpg2.battles, dpg2.recent_dpg
   from dpg2
   where tank_id=545 and battles >= 150
   order by dpg desc
   limit 50;

具有以下輸出：

"Limit  (cost=1523898.99..1523899.12 rows=50 width=28) (actual time=23950.831..23950.838 rows=50 loops=1)"
"  ->  Sort  (cost=1523898.99..1524200.69 rows=120678 width=28) (actual time=23950.831..23950.833 rows=50 loops=1)"
"        Sort Key: dpg2.dpg"
"        Sort Method: top-N heapsort  Memory: 32kB"
"        ->  Bitmap Heap Scan on dpg2  (cost=13918.06..1519890.16 rows=120678 width=28) (actual time=434.791..23922.872 rows=21963 loops=1)"
"              Recheck Cond: (tank_id = 545)"
"              Filter: (battles >= 150)"
"              Rows Removed by Filter: 1060576"
"              Heap Blocks: exact=458918"
"              ->  Bitmap Index Scan on dpg_tank_id_idx  (cost=0.00..13887.89 rows=967310 width=0) (actual time=299.796..299.796 rows=1082539 loops=1)"
"                    Index Cond: (tank_id = 545)"
"              SubPlan 1"
"                ->  Index Scan using players2_pkey on players2  (cost=0.43..5.45 rows=1 width=10) (actual time=0.105..0.105 rows=1 loops=21963)"
"                      Index Cond: (account_id = dpg2.account_id)"
"Planning time: 0.212 ms"
"Execution time: 23953.952 ms"

真正讓我感到困惑的是，查詢規劃器正試圖沿著 dpg 列使用，而它所要做的就是遍歷與給定 tank_id 對應的索引並選擇滿足“battles >= 150”條件的前 50 個條目.

事實上，簡單地刪除“order by”子句，我在 2 秒內得到相同的結果，因為它最終使用了索引，該索引按我想要的順序排序。

我知道我已經解決了我的問題，但Postgres 為什麼要這樣做，可能的解決方案是什麼？

編輯：我已經在桌子上執行了分析並且 autovacuum 已打開。

PostgreSQL 認為它不能使用定義的索引(tank_id, dpg DESC NULLS LAST)來滿足這個沒有排序的查詢。

如果只是DESC，那很好。或者如果它只是 on (tank_id, dpg)，那也可以工作（它會向後掃描索引的相關部分）。

如果您無法更改索引的定義，那麼使查詢匹配現有索引也可能會起作用，因此：

where tank_id=545 and battles >= 150
order by dpg desc nulls last
limit 50

（這大概是你想要的吧？）

要按照最初使用您最初擁有的索引編寫的查詢來執行查詢，它必須分兩部分執行查詢。首先它必須訪問 tank_id=545 區域末尾的 NULL（因為沒有進一步限定的 ORDER BY…DESC 隱含意味著 NULLS FIRST），然後如果它還沒有達到 LIMIT 將跳轉到tank_id=545 區域的開頭以完成 ORDER BY DESC 部分。

因此，參與的必須是執行者，而不僅僅是計劃者。要做到這一點需要大量煩人的程式碼，而且沒有人自願編寫它。（此外，它可能是難以發現的錯誤的豐富來源，因此即使有人編寫了必要的程式碼，它也可能不會被程式碼庫接受）

引用自：https://dba.stackexchange.com/questions/114908