Postgres：使用索引時，帶有 ORDER BY 的 SELECT 速度要慢 4 倍

我有一個有 4 列的表：

id SERIAL PRIMARY KEY
dt DATE
val bigint  -- Already has a btree index
txt VARCHAR(64)

我正在執行一個查詢：

EXPLAIN ANALYSE SELECT dt, val FROM temp_test ORDER BY dt;

這大約需要 13 毫秒，以下是EXPLAIN ANALYZE

Sort  (cost=2862269.49..2912269.54 rows=20000020 width=12) (actual time=10561.801..12214.518 rows=20000020 loops=1)
 Sort Key: dt
 Sort Method: external merge  Disk: 508816kB
 ->  Seq Scan on temp_test  (cost=0.00..436917.25 rows=20000020 width=12) (actual time=0.016..2451.364 rows=20000020 loops=1)
Planning time: 0.127 ms
Execution time: 12874.978 ms

我認為創建索引dt會加快速度，我做到了

CREATE INDEX temp_test_dt_btree ON temp_test(dt);

創建索引後，我再次執行相同的查詢：

EXPLAIN ANALYSE SELECT dt, val FROM temp_test ORDER BY dt;

這個耗時約 54 秒，大約是沒有索引的 4 倍，輸出如下EXPLAIN ANALYSE

Index Scan using temp_test_dt_btree on temp_test  (cost=0.44..1317015.07 rows=20000020 width=12) (actual time=0.049..53027.137 rows=20000020 loops=1)
Planning time: 0.241 ms
Execution time: 53742.120 ms

我有點困惑，因為我預計該指數充其量只能提高性能，而在最壞的情況下幾乎沒有（或沒有）影響。

我在跑步：

PostgreSQL 10.11 (Ubuntu 10.11-1.pgdg18.04+1)

如果您的表是完全真空的，您可以通過創建索引來獲得僅索引掃描：

CREATE INDEX ON temp_test(dt,val);

這可能會快很多，因為它根本不需要訪問表。（在我的測試中，它比排序快了大約 2.5 倍。）

至於為什麼它使用您已經擁有的索引選擇錯誤的計劃，可能只是您的有效記憶體大小設置得太高而無法實際可用的 RAM 量。您將不得不告訴我們更多關於您的設置和硬體的資訊。

我在沒有 index-only-scan 索引的情況下複製了您的發現，計劃者選擇了慢得多的有序索引掃描而不是 seq 掃描。我在 AWS m5.large 機器上複製了它，使用 Ubuntu 18.04，使用 ubuntu 儲存庫中的 PostgreSQL 10，並使用 OS 和 PostgreSQL 的預設設置，而不是設置 track_io_timing 和 max_parallel_workers_per_gather=0; 像這樣設置它：

create table temp_test (
 id SERIAL PRIMARY KEY,
 dt DATE,
 val bigint,
 txt VARCHAR(64)
);
insert into temp_test select x, now()+interval '10 years'*random(), random()*1000000000, random()::text from generate_series(1,20000000) f(x);
vacuum ANALYZE temp_test ;

使用 128MB 的預設 shared_buffers，有序索引掃描是規劃器的首選，但正如您所指出的那樣慢，即使所有數據都在記憶體中（vmstat 在查詢期間顯示沒有讀/寫活動）。但是，如果增加 shared_buffers 以便它保存所有數據，那麼有序索引掃描會變得更快（仍然比排序慢，但幾乎沒有那麼多）。

所以主要的問題是從文件系統記憶體中讀取數據到shared_buffers的速度很慢，而規劃器沒有考慮到這一點。實現 Effective_cache_size 的程式碼假定讀取已經在記憶體中的頁面是完全免費的。現實情況是，雖然從文件系統記憶體中讀取頁面比從磁碟中讀取頁面要便宜得多，但相對於 cpu_tuple_cost 而言，它仍然是相當昂貴的。所以“免費”幾乎不准確。

規劃器沒有公開任何您可以調整的旋鈕，以使其考慮從文件系統記憶體中讀取頁面的剩餘成本，因此它看起來不是免費的。並且添加這樣一個旋鈕並不容易，因為要準確地做到這一點，您需要區分在 shared_buffers 中將重新找到多少頁面，在文件系統記憶體中但在 share_buffers 中沒有重新找到多少頁面. 目前的“Mackert 和 Lohman 公式”沒有做出這樣的區分，我不清楚如何做到這一點。

僅當您選擇一小組記錄時，使用索引才會很快。您想要整個表，數據庫正在浪費時間在索引和數據之間切換。

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