優化點陣圖堆掃描

我試圖理解為什麼我的查詢需要很長時間，即使我已經索引了所需的列：

SELECT entity_id,
      id,
      report_date
FROM own_inst_detail
WHERE ( own_inst_detail.id = 'P7M7WC-S' )
 AND ( own_inst_detail.report_date >= '2017-02-01T17:29:49.661Z' )
 AND ( own_inst_detail.report_date <= '2018-08-01T17:29:49.663Z' )

的記憶體結果EXPLAIN ANALYZE如下：

Bitmap Heap Scan on own_inst_detail (cost=20.18..2353.55 rows=597 width=22) (actual time=1.471..6.955 rows=4227 loops=1)
 Recheck Cond: ((id = 'P7M7WC-S'::bpchar) AND (report_date >= '2017-06-01'::date) AND (report_date <= '2018-08-01'::date))
 Heap Blocks: exact=4182
 ->  Bitmap Index Scan on own_inst_detail  (cost=0.00..20.03 rows=597 width=0) (actual time=0.901..0.901 rows=4227 loops=1)
       Index Cond: ((id = 'P7M7WC-S'::bpchar) AND (report_date >= '2017-06-01'::date) AND (report_date <= '2018-08-01'::date))
Planning time: 0.123 ms
Execution time: 7.801 ms

這部分查詢花費了我完整查詢所花費的 5 秒中的 4 秒。

我已經合併了id和report_date的索引。對於這些列，我也有兩個獨立的索引。

我嘗試過設置高work_mem以及降低random_page_cost 但沒有任何幫助。

非常感謝任何其他建議。

我發現了類似的問題How to index WHERE (start_date >= ‘2013-12-15’)建議添加 B-Tree 索引，但我已經有了report_date的索引。

創建表腳本：

CREATE TABLE IF NOT EXISTS public.own_inst_detail (
   entity_id character(8) NOT NULL,
   id character(8) NOT NULL,
   report_date date NOT NULL,
   PRIMARY KEY(report_date)
);

指數：

CREATE INDEX indx_own_inst_detail_report_date_desc ON own_inst_detail (report_date DESC NULLS LAST)

CREATE INDEX indx_own_inst_detail_id_report_date_desc ON own_inst_detail (id, report_date DESC NULLS LAST)

數據類型

數據類型character(n)幾乎總是錯誤的選擇。這是我不會再使用的“傳統”類型。它表現出令人驚訝的行為並且什麼都不做text//不能做得更好varchar。varchar(n)

• 使用數據類型“文本”儲存字元串有什麼缺點嗎？

並且您將date表中的類型與timestamp查詢中的文字混合在一起。雖然這可行，但您至少應該提供顯式類型聲明以防止出現意外結果。更好的是，在查詢中提供實際*日期或顯式轉換輸入。*像

report_date >= timestamp '2017-02-01T17:29:49.661Z'

或者：

report_date >= date '2017-02-02'

關於常量和類型轉換的手冊。

指數

indx_own_inst_detail_id_report_date_desc您的索引(id, report_date DESC NULLS LAST) 看起來很適合查詢，並且您提供的查詢計劃很好地利用了它。Execution time: 7.801 ms似乎也不算太糟糕。

如果您的“未記憶體”查詢需要 4 秒，那麼您可能需要處理硬體或伺服器配置或兩者兼而有之。儲存速度慢且沒有足夠的RAM 用於記憶體？更多work_mem不是解決這個問題的方法，甚至可能通過從記憶體中取出 RAM 使情況變得更糟。有關的：

• 在 Postgres 9.2 上增加 work_mem 和 shared_buffers 會顯著降低查詢速度

如果您有足夠的 RAM 和適當的記憶體設置，這可能會導致冷記憶體問題：只有第一次呼叫很慢（或前幾次呼叫）。如果這是一個問題，請考慮pg_prewarm。看：

• PostgreSQL：強制數據進入記憶體

如果你的表被清理得足夠多（或者大部分是只讀的），如果你將列表中的一個附加列附加到索引中，你可能會從僅索引**掃描**中受益：entity_id``SELECT

CREATE INDEX ON own_inst_detail (id, report_date DESC NULLS LAST, entity_id)

這在您的情況下可能特別有用，因為 Postgres 在這種情況下只訪問索引，根本不需要訪問表。（完全消除Bitmap Heap Scan。）可能有助於解決您的磁碟/冷記憶體瓶頸。

有關的：

• Postgres 可以對這個帶有連接表的查詢使用僅索引掃描嗎？

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