SELECT 查詢中未使用索引

我在 Postgres 9.4.1 中有一個大約 325 萬行的表格，格式如下

CREATE TABLE stats
(
   id serial NOT NULL,
   type character varying(255) NOT NULL,
   "references" jsonb NOT NULL,
   path jsonb,
   data jsonb,
   "createdAt" timestamp with time zone NOT NULL,
   CONSTRAINT stats_pkey PRIMARY KEY (id)
)
WITH (
   OIDS=FALSE
);

這type是一個不超過 50 個字元的簡單字元串。

該references列是具有鍵值列表的對象。基本上任何簡單鍵值列表，並且只有 1 級深度，值始終是字元串。它可能是

{
   "fruit": "plum"
   "car": "toyota"
}

或者它可能是

{
   "project": "2532"
}

createdAt時間戳並不總是從數據庫生成（但如果未提供值，預設情況下會生成）

我目前正在使用僅包含測試數據的表格。在此數據中，每一行都有一個project鍵作為參考。所以有 325 萬行帶有一個項目鍵。正好有 400,000 個不同的值可供project參考。該欄位只有 5 個不同的值，type在生產中可能不會超過幾百個。

所以我試圖索引表以快速執行以下查詢：

SELECT
 EXTRACT(EPOCH FROM (MAX("createdAt") - MIN("createdAt"))) 
FROM
 stats
WHERE
 stats."references"::jsonb ? 'project' AND
 (
   stats."type" = 'event1' OR
   (
     stats."type" = 'event2' AND
     stats."createdAt" > '2015-11-02T00:00:00+08:00' AND
     stats."createdAt" < '2015-12-03T23:59:59+08:00'
   )
 )
GROUP BY stats."references"::jsonb->> 'project'

該查詢基於具有相同引用的兩個統計數據行返回兩個事件之間的時間距離。在這種情況下project。每個type和選定的reference值只有 1 行，但也可能沒有行，在這種情況下返回的結果為 0（稍後在更大查詢的不同部分中取平均值）。

我已經在createdAt typeandreferences列上創建了一個索引，但查詢執行計劃似乎正在執行完整掃描。

指數

CREATE INDEX "stats_createdAt_references_type_idx"
   ON stats
   USING btree
   ("createdAt", "references", type COLLATE pg_catalog."default");

執行計劃：

HashAggregate  (cost=111188.31..111188.33 rows=1 width=38) 
               (actual time=714.499..714.499 rows=0 loops=1)
  Group Key: ("references" ->> 'project'::text)
     ->  Seq Scan on stats  (cost=0.00..111188.30 rows=1 width=38) 
                            (actual time=714.498..714.498 rows=0 loops=1)
         Filter: (
             (("references" ? 'project'::text) 
              AND ((type)::text = 'event1'::text)) OR 
             (((type)::text = 'event2'::text) 
              AND ("createdAt" > '2015-11-02 05:00:00+13'::timestamp with time zone) 
              AND ("createdAt" < '2015-12-04 04:59:59+13'::timestamp with time zone)))

Rows Removed by Filter: 3258680
Planning time: 0.163 ms
Execution time: 714.534 ms

我真的不太了解索引和查詢執行計劃，所以如果有人能指出我正確的方向，那就太好了。

編輯

正如 Erwin 所指出的，即使我確實有正確的索引，表掃描仍然會發生，因為從查詢返回的表部分非常大。這是否意味著對於這組數據，這是我可以獲得的最快查詢時間？我假設如果我在沒有項目引用的情況下添加了 60M 更多不相關的行，它可能會使用索引（如果我有正確的索引），但我不知道如何通過添加更多數據來加快查詢速度。也許我錯過了一些東西。

根據您目前的解釋，索引對您目前的查詢沒有太大幫助（如果有的話）。

所以有 325 萬行帶有一個項目鍵。

這也是總行數，所以這個謂詞適用true於（幾乎）每一行……而且根本沒有選擇性。jsonb但是該列沒有有用的索引"references"。將它包含在btree索引中("createdAt", "references", type)是沒有意義的。

即使你有一個通常更有用的 GIN 索引，"reference"比如：

CREATE INDEX stats_references_gix ON stats USING gin ("references");

… Postgres 仍然沒有關於列內各個鍵的有用統計資訊jsonb。

---

只有 5 個不同的值type

您的查詢選擇了所有一種類型和另一種類型的未知部分。估計佔所有行的 20-40%。順序掃描肯定是最快的計劃。索引開始對大約 5% 或更少的行有意義。

要進行測試，您可以通過在會話中設置調試目的來強制使用可能的索引：

SET enable_seqscan = off;

重置：

RESET enable_seqscan;

您會看到較慢的查詢…

---

您按項目值分組：

GROUP BY "references"->> 'project'

和：

項目引用正好有 400,000 個不同的值。

平均每個項目有 8 行。如果我們只在 LATERAL 子查詢中選擇每個項目的最小值和最大值，則根據值頻率，我​​們仍然必須檢索所有行的估計 3 - 20% …

試試這個索引，它比你現在擁有的更有意義：

CREATE INDEX stats_special_idx ON stats (type, ("references" ->> 'project'), "createdAt")
WHERE "references" ? 'project';

Postgres 可能仍會退回到順序掃描…

使用規範化模式/更具選擇性的標準/只選擇最小值和最大值的更智能的查詢可以完成更多工作"createdAt"……

詢問

我會這樣寫你的查詢：

SELECT EXTRACT(EPOCH FROM (MAX("createdAt") - MIN("createdAt"))) 
FROM   stats
WHERE  "references" ? 'project'
AND   (type = 'event1' OR
      type = 'event2'
  AND "createdAt" >= '2015-11-02 00:00:00+08:00'  -- I guess you want this
  AND "createdAt" <  '2015-12-04 00:00:00+08:00'
     )
GROUP  BY "references"->> 'project';  -- don't cast

筆記

• 不要在這裡投射：

stats."references"::jsonb ? 'project'

專欄jsonb已經是，你一無所獲。如果謂詞是選擇性的，則轉換可能會禁止索引使用。

• 您的謂詞在下限和上限"createdAt"可能*不正確。*要包括一整天，請考慮我建議的替代方案。
• references是一個保留字，所以你必須總是用雙引號引起來。不要將其用作標識符。類似於雙引號的 CaMeL 案例名稱，如"createdAt"兩者之一。允許，但容易出錯，不必要的複雜化。
• type

type character varying(255) NOT NULL,

類型是不超過 50 個字元的簡單字元串。

type 欄位只有 5 個不同的值，在生產中可能不會超過幾百個。

這一切似乎都毫無意義。

• varchar(255)本身幾乎沒有任何意義。255 個字元是 Postgres 中沒有意義的任意限制。
• 如果它不超過 50 個字元，那麼 255 的限制就更沒有意義了。
• 在適當規範化的設計中，您將有一個小integer列type_id（引用一個小type表），每行僅佔用 4 個字節，並使索引更小更快。

• 理想情況下，您應該有一個project表格，列出所有項目和另一個小整數 FKproject_id列stats。將使任何此類查詢更快。對於選擇性標準，即使沒有建議的規範化，也可以進行更快的查詢*。*沿著這些構想：
• 優化 GROUP BY 查詢以檢索每個使用者的最新記錄

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