Postgres 使用 MAX 和 groupBy 索引查詢

有沒有辦法索引以下查詢？

SELECT run_id, MAX ( frame ) , MAX ( time ) FROM run.frames_stat GROUP BY run_id;

我嘗試在 and 上創建排序（非複合）索引frame，time並在 上創建索引run_id，但查詢規劃器不使用它們。

雜項資訊：

• 不幸的是（由於我不會進入的原因）我無法更改查詢
• 該frames_stat表有 4200 萬行
• 該表是不變的（不會發生進一步的插入/刪除）
• 查詢總是很慢，只是變慢了，因為這個數據集比過去大。
• 表上沒有索引
• 我們正在使用 Postgres 9.4
• db 的“work_mem”大小為 128MB（如果相關）。
• 硬體：130GB 記憶體，10 核 Xeon

架構：

CREATE TABLE run.frame_stat (
 id bigint NOT NULL,
 run_id bigint NOT NULL,
 frame bigint NOT NULL,
 heap_size bigint NOT NULL,
 "time" timestamp without time zone NOT NULL,
 CONSTRAINT frame_stat_pkey PRIMARY KEY (id)
)

解釋分析：

HashAggregate  (cost=1086240.000..1086242.800 rows=280 width=24) (actual time=14182.426..14182.545 rows=280 loops=1)
 Group Key: run_id
 ->  Seq Scan on zulu  (cost=0.000..770880.000 rows=42048000 width=24) (actual time=0.037..4077.182 rows=42048000 loops=1)

太糟糕了

如果您根本無法更改查詢，那就太糟糕了。你不會得到一個好的解決方案。如果您沒有對錶進行表限定（獲得卓越的性能。run.frames_statsearch_path

這是這種技術的秘訣：

• 如何在 PostgreSQL 中為單元測試偽造 inet_client_addr()？

@Joishi 的想法是RULE（絕望的）最後手段。但我寧願不去那裡。意外行為的陷阱太多。

更好的查詢/索引

如果您可以更改查詢，您應該嘗試模擬鬆散的索引掃描：

• 優化 GROUP BY 查詢以檢索每個使用者的最新記錄

當基於**每個相關的一行的單獨表run_id**時，這甚至更有效- 讓我們稱之為run_tbl。如果您還沒有，請創建它！

使用相關子查詢實現：

SELECT run_id
   , (SELECT frame
      FROM   run.frames_stat
      WHERE  run_id = r.run_id
      ORDER  BY frame DESC NULLS LAST
      LIMIT  1) AS max_frame
   , (SELECT "time"
      FROM   run.frames_stat
      WHERE  run_id = r.run_id
      ORDER  BY "time" DESC NULLS LAST
      LIMIT  1) AS max_time
FROM   run_tbl r;

創建兩個具有匹配排序順序的多列索引以提高性能：

CREATE index fun_frame_idx ON run.frames_stat (run_id, frame DESC NULLS LAST);
CREATE index fun_frame_idx ON run.frames_stat (run_id, "time" DESC NULLS LAST);

NULLS LAST只有當可以有空值時才需要。但無論哪種方式都不會受到傷害。

• 日期查詢範圍內未使用的索引

只有 280 個 distinct run_id，這將非常快。

物化視圖

或者，根據這些關鍵資訊：

“frames_stat”表有 4200 萬行

rows=280 – 返回的行數 = 不同的 run_id

該表是不變的（沒有插入/刪除）

使用 a MATERIALIZED VIEW，它會很小（只有 280 行）並且超級快。

您仍然需要更改查詢以使其基於 MV 而不是表。

另外：永遠不要使用保留字time（在標準 SQL 中）作為標識符。

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