優化“WHERE x BETWEEN a AND b GROUP BY y”查詢

CREATE TABLE test_table
(
 id uuid NOT NULL,
 "RefId" uuid NOT NULL,
 "timestampCol" timestamp without time zone NOT NULL,
 "bigint1" bigint NOT NULL,
 "bigint2" bigint NOT NULL,
 "int1" integer NOT NULL,
 "int2" integer NOT NULL,
 "bigint3" bigint NOT NULL,
 "bigint4" bigint NOT NULL,
 "bigint5" bigint NOT NULL,
 "hugeText" text NOT NULL,
 "bigint6" bigint NOT NULL,
 "bigint7" bigint NOT NULL,
 "bigint8" bigint NOT NULL,
 "denormalizedData" jsonb NOT NULL,
 "textCol" text NOT NULL,
 "smallText" text NOT NULL,
 "createdAt" timestamp with time zone NOT NULL,
 "updatedAt" timestamp with time zone NOT NULL,
 CONSTRAINT test_pkey PRIMARY KEY (id)
);

SELECT "textCol", SUM("bigint1"), SUM("bigint2") -- etc, almost every single column gets aggregated
FROM "test_table"
WHERE "timestampCol" BETWEEN '2016-06-12' AND '2016-06-17'
GROUP BY "textCol"
ORDER BY SUM("bingint2"), SUM("bigint3") DESC -- the ORDER BY columns are dynamic, but there's only 4 possible combination of columns.
LIMIT 50;

請糾正我的理解不正確的地方。在 Postgres 中，我可以利用 ontimestampCol或 on的索引textCol，**但不能同時利用兩者？**我粘貼的查詢計劃僅用於顯示 Postgres 選擇的算法。真正的表有幾百萬行，而不僅僅是~66,000。

CREATE INDEX timestamp_col_index on test_table using btree (“timestampCol”)

上的索引（btree）`"timestampCol"`意味著查詢計劃器將對整個數據集進行切片，以僅在使用 a或 a將行分組之前`'2016-06-12'`和之前保留行。`'2016-06-17'``Hash Join``Sort + GroupAggregate``textCol`

GroupAggregate (cost=3925.50..4483.19 rows=22259 width=41) (actual time=80.764..125.342 rows=22663 loops=1) Group Key: “textCol” -> Sort (cost=3925.50..3981.45 rows=22380 width=41) (actual time=80.742..84.915 rows=22669 loops=1) Sort Key: “textCol” Sort Method: quicksort Memory: 2540kB -> Index Scan using timestamp_col_index on test_table (cost=0.29..2308.56 rows=22380 width=41) (actual time=0.053..13.939 rows=22669 loops=1) Index Cond: ((“timestampCol” >= ‘2016-06-12 00:00:00’::timestamp without time zone) AND (“timestampCol” <= ‘2016-06-17 00:00:00’::timestamp without time zone))

2. ```
CREATE INDEX text_col_index on test_table using btree ("textCol")

上的索引（btree）textCol意味著查詢計劃程序已經“預先分組”了行，但它必須遍歷索引中的每一行以過濾掉那些不匹配的行timestampCol BETWEEN timestamp1 AND timestamp2。

GroupAggregate  (cost=0.42..16753.91 rows=22259 width=41) (actual time=0.281..127.047 rows=22663 loops=1)
 Group Key: "textCol"
 ->  Index Scan using text_col_index on test_table  (cost=0.42..16252.18 rows=22380 width=41) (actual time=0.235..76.182 rows=22669 loops=1)
       Filter: (("timestampCol" >= '2016-06-12 00:00:00'::timestamp without time zone) AND ("timestampCol" <= '2016-06-17 00:00:00'::timestamp without time zone))
       Rows Removed by Filter: 43719

3. 創建這兩個索引意味著 Postgres 將執行成本分析來決定它認為 1. 和 2. 中的哪一個最快。但它永遠不會同時利用這兩個索引。
4. 創建多列索引不會有任何幫助。從我的測試來看，Postgres 根本不會改變它的查詢計劃，無論是("textCol", "timestampCol")還是("timestampCol", "textCol").
5. 我已經嘗試了btree_ginandbtree_gist擴展，但我從來沒有能夠讓查詢規劃器保持行“預先分組”或利用與 1. 和 2 相比在 ~4,000,000 行規模上獲得不錯的速度增益。也許我是沒有正確使用它們？我將如何建構這些索引並使我的查詢適應它？

請讓我知道我可能會誤解什麼。如何針對包含幾百萬行的表優化這樣的查詢？

有關資料結構的重要資訊：

• BETWEEN 中使用的時間戳有 99% 的時間類似於“過去 2 週”或“上個月”。在某些情況下，BETWEEN 最終會選擇多達 99% 的行，但很少會選擇 100% 的行。
• 該textCol列可以是令人難以置信的變化或令人難以置信的規則。在某些情況下，假設 300 萬行中會有 290 萬個唯一textCol值。在其他情況下，對於相同數量的行，只有 30,000-100,000 個唯一textCol值。

我正在使用 Postgres 9.4，但只要性能提升可以證明它的合理性，升級到 9.5 是可行的。

理念一

從它們的名稱來看，這些列"denormalizedData"似乎"hugeText"比較大，可能是查詢中涉及的列的許多倍。大小對於像這樣的大查詢很重要。非常大的值 (> 2kb)text或jsonb得到“toasted”，這可以避免最壞的情況。但即使是內聯儲存的餘數或更小的值也可能是與查詢相關的列的數倍，這些列跨越大約 100 個字節。

有關的：

• 為讀取性能配置 PostgreSQL

將與查詢相關的列拆分為單獨的 1:1 表可能會有很長的路要走。（取決於完整的情況。您為另一個行標題和另一個 PK 添加了一些儲存成本，並且寫入表會變得更加複雜和昂貴。）

想法 2

此外（就像您確認的那樣）只有 4 列與確定前 50 名相關。

您可能有一個角度來獲得一個小得多的物化視圖 (MV)，其中僅包含這些列加上"timestampCol"並且"textCol"僅"last 2 weeks" or "last month"包含數據。對 MV 執行快速查詢以辨識前 50 個"textCol"並僅從大表中檢索這些行。或者，準確地說，只是您的 MV 中未包含的其他列 - 您在第一步中獲得了這些列的總和。

("textCol")您只需要為大表創建一個索引。另一個("timestampCol")用於 MV - 僅用於帶有選擇性WHERE子句的查詢實例。否則，順序掃描整個 MV 會更便宜。

如果您的許多查詢涵蓋相同的時間段，您可能會更進一步："textCol"在 MV 中只保存一行，並預先匯總總和（對於幾個常用的時間段，可能有兩個或更多 MV）。你明白了。那應該快得多。

您甚至可以使用整個結果集創建 MV，並在當天的第一個新查詢之前刷新。

根據確切的數字，您可以將這兩種想法結合起來。

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