由於索引掃描的重新檢查條件導致查詢性能不佳

我們正在測試儲存在 JSONB 中的元數據與使用 PostgreSQL 9.4 伺服器的傳統 EAV 目錄（通常用於生物醫學場景）相比的性能。

嘗試通過適當的索引提高 EAV 性能時遇到問題。這是我在其他表上的其他查詢中也遇到過的問題，所以我擔心我們犯了一些根本性錯誤。

為了模擬 EAV，我有三個表：

1)data包含所有實體（未顯示）

2）eav_attribute包含所有屬性

CREATE TABLE eav_attribute (
   id serial PRIMARY KEY,
   data_type integer NOT NULL,
   loop integer,
   name text NOT NULL,
   field_type text NOT NULL,
   has_unit boolean,
   created_at timestamp with time zone NOT NULL,
   updated_at timestamp with time zone NOT NULL
);

2)eav_value_text_data包含所有值

CREATE TABLE eav_value_text_data (
id serial PRIMARY KEY,
entity integer NOT NULL,
attribute integer NOT NULL,
value text NOT NULL,
created_at timestamp with time zone NOT NULL,
updated_at timestamp with time zone NOT NULL
);

使用與最後兩個表相關聯的以下外鍵約束（其他外鍵未顯示，因為它們未在我嘗試的查詢中使用）：

ALTER TABLE ONLY eav_value_text_data
ADD CONSTRAINT eav_value_text_data_attribute_fkey
 FOREIGN KEY (attribute) REFERENCES eav_attribute(id);

我用龐大的基因組變異數據集填充了這兩個表。對於數據eav_value_text_data表中的 2500 萬行和 49 個不同的屬性（中的行） ，我在表上有超過 2.5 億行。eav_attribute

現在我正在檢查以下查詢的成本和性能：

SELECT count(*) FROM eav_value_text_data v 
INNER JOIN eav_attribute a ON a.id = v.attribute 
WHERE a.data_type = 11 
AND a.name = 'id' 
AND v.value = 'rs145368920';

值“rs145368920”僅出現 15 次eav_value_text_data

首先，我執行 EXPLAIN ANALYZE 而不向兩個表添加任何索引：

查詢計劃

Aggregate  (cost=5109969.74..5109969.75 rows=1 width=0) (actual time=351086.638..351086.639 rows=1 loops=1)
  ->  Nested Loop  (cost=0.00..5109969.32 rows=168 width=0) (actual time=34814.603..351086.541 rows=15 loops=1)
        Join Filter: (a.attribute = b.id)
        ->  Seq Scan on eav_attribute b  (cost=0.00..1.73 rows=1 width=4) (actual time=0.027..0.034 rows=1 loops=1)
              Filter: ((data_type = 11) AND (name = 'id'::text))
              Rows Removed by Filter: 48
        ->  Seq Scan on eav_value_text_data a  (cost=0.00..5109864.40 rows=8255 width=4) (actual time=34814.555..351086.380 rows=15 loops=1)
              Filter: (value = 'rs145368920'::text)
              Rows Removed by Filter: 252054702
Planning time: 145.614 ms
Execution time: 351096.291 ms

它對兩個表執行順序掃描，並且需要 350 秒的非常糟糕的時間。現在，我在 的列屬性上添加索引eav_value_text_data以提高查詢性能：

CREATE INDEX eav_value_text_data_attribute_index ON eav_value_text_data (attribute);

然後我再次執行 EXPLAIN ANALYZE：

查詢計劃

Aggregate  (cost=2726067.17..2726067.18 rows=1 width=0) (actual time=1262829.858..1262829.859 rows=1 loops=1)
  ->  Nested Loop  (cost=423172.71..2726066.76 rows=166 width=0) (actual time=113152.884..1262829.758 rows=15 loops=1)
        ->  Seq Scan on eav_attribute b  (cost=0.00..1.73 rows=1 width=4) (actual time=0.027..0.062 rows=1 loops=1)
              Filter: ((data_type = 11) AND (name = 'id'::text))
              Rows Removed by Filter: 48
        ->  Bitmap Heap Scan on eav_value_text_data a  (cost=423172.71..2726057.61 rows=741 width=4) (actual time=113152.821..1262829.557 rows=15 loops=1)
              Recheck Cond: (attribute = b.id)
              Rows Removed by Index Recheck: 223460596
              Filter: (value = 'rs145368920'::text)
              Rows Removed by Filter: 24188192
              Heap Blocks: exact=37881 lossy=1921273
              ->  Bitmap Index Scan on eav_value_text_data_attribute_index  (cost=0.00..423172.52 rows=22914127 width=0) (actual time=14177.368..14177.368 rows=24188207 loops=1)
                    Index Cond: (attribute = b.id)
Planning time: 126.846 ms
Execution time: 1262840.302 ms

即使它現在正在執行索引掃描，它現在也需要超過 1260 秒，這比沒有索引時差近 4 倍。這裡有什麼問題？是否與大量有損堆塊有關？可以通過調整work_mem來解決嗎？所有查詢都使用冷記憶體執行（停止 Postgres 伺服器並刷新記憶體）以避免記憶體影響。

目前，我們在執行查詢的系統上有 4GB 的 RAM。我們已分配：

work_mem = 4 MB

shared_buffers = 950 MB（大約 25% 總 RAM） Effective_cache_size =

2600 MB（大約 70% 總 RAM）

由於大表eav_value_text_databig table 上的選擇性謂詞是 ，因此您比其他任何東西都v.value = 'rs145368920'需要索引。**value**上的索引attribute幾乎不相關 - 僅與第一個結合使用以允許僅索引掃描：

CREATE INDEX eav_value_text_data_val_att_idx ON eav_value_text_data (value, attribute);

這應該會有很大的不同。

value應該是索引中的第一列。更多的：

• 複合索引是否也適用於第一個欄位的查詢？

索引 md5 雜湊為uuid

由於事實證明您的value列太大而無法被索引，因此您決定改用md5 雜湊（這是一個很好的解決方案）：

• 字元變化索引成本和長度限制

考慮將 md5 雜湊儲存為uuid，這將是最有效的（更小、更快的儲存）。您可以將結果轉換為uuid：

md5(value)::uuid

此答案最後一章中的詳細資訊：

• 將文本表示中的十六進制轉換為十進制數

有關的：

• 當所有值都是 36 個字元時，使用 char 與 varchar 進行索引查找會明顯更快嗎
• 如何在 Postgres 9.6 中的 uuid 上添加 PostgreSQL GIN 索引

您的索引將如下所示：

CREATE INDEX eav_value_special_idx
ON eav_value_text_data (cast(md5(value) AS uuid), attribute);

請注意cast()索引定義中的顯式語法：

• 如何在 postgres 中的整數 json 屬性上創建索引

您的查詢仍然可以使用速記語法：

SELECT count(*)
FROM   eav_value_text_data v 
JOIN   eav_attribute a ON a.id = v.attribute 
WHERE  a.data_type = 11 
AND    a.name = 'id' 
AND    **md5(v.value)::uuid = md5('rs145368920')::uuid;**

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