PostgreSQL：WHERE 子句中的 ANY (VALUES(…)) 導致速度急劇下降
好的，我之前問過一個關於大型數據集的問題，但從未得到回答，所以我決定削減它並詢問以前設置的較小子集，並簡化我在一個新問題中嘗試完成的任務 - 希望這會清楚一點。
我
report_drugs在磁碟上有一個 1775 MB 的大表 ( )，包含超過 3300 萬行。表佈局：Column | Type | Modifiers ---------------+-----------------------------+----------- rid | integer | not null drug | integer | not null created | timestamp without time zone | reason | text | duration | integer | drugseq | integer | effectiveness | integer | Indexes: "report_drugs_drug_idx" btree (drug) CLUSTER "report_drugs_drug_rid_idx" btree (drug, rid) "report_drugs_reason_idx" btree (reason) "report_drugs_reason_rid_idx" btree (reason, rid) "report_drugs_rid_idx" btree (rid)如您所見，我有幾個索引（並非都與這個問題相關），並且
CLUSTER在列索引上編輯了表，drug因為這主要用於範圍。在獲取任何指標之前，該表也是VACUUM ANALYZE由我自動和手動完成的。然而像這樣的簡單查詢：
SELECT drug, reason FROM report_drugs WHERE drug = ANY(VALUES (9557), (17848), (17880), (18223), (18550), (19020), (19084), (19234), (21295), (21742), (23085), (26017), (27016), (29317), (33566), (35818), (37394), (39971), (41505), (42162), (44000), (45168), (47386), (48848), (51472), (51570), (51802), (52489), (52848), (53663), (54591), (55506), (55922), (57209), (57671), (59311), (62022), (62532), (63485), (64134), (66236), (67394), (67586), (68134), (68934), (70035), (70589), (70896), (73466), (75931), (78686), (78985), (79217), (83294), (83619), (84964), (85831), (88330), (89998), (90440), (91171), (91698), (91886), (91887), (93219), (93766), (94009), (96341), (101475), (104623), (104973), (105216), (105496), (106428), (110412), (119567), (121154));將需要超過 7 秒才能完成並具有以下查詢計劃：
Nested Loop (cost=1.72..83532.00 rows=24164 width=26) (actual time=0.947..7385.490 rows=264610 loops=1) -> HashAggregate (cost=1.16..1.93 rows=77 width=4) (actual time=0.017..0.036 rows=77 loops=1) Group Key: "*VALUES*".column1 -> Values Scan on "*VALUES*" (cost=0.00..0.96 rows=77 width=4) (actual time=0.001..0.007 rows=77 loops=1) -> Index Scan using report_drugs_drug_idx on report_drugs (cost=0.56..1081.67 rows=314 width=26) (actual time=0.239..95.568 rows=3436 loops=77) Index Cond: (drug = "*VALUES*".column1) Planning time: 7.009 ms Execution time: 7393.408 ms我在
ANY(VALUES(..))子句中添加的值越多，它變得越慢。此查詢有時可能包含超過 200 個值，然後需要 30 多秒才能完成。然而，僅包含幾個值（4 個 ex.）可以在 200 毫秒內給我一個查詢。因此，顯然是該WHERE條款的這一部分導致了這種放緩。我可以做些什麼來使這個查詢更好地執行？我在這裡遺漏了哪些明顯的要點？
我的硬體和數據庫設置：
我正在從 SSD 驅動器執行集群。該系統總記憶體為 24 GB，在 Debian 上執行，使用 4Ghz 8 核 i7-4790 處理器。對於這種數據集應該有足夠的硬體。
一些重要的
postgresql.conf讀數：
- 共享緩衝區 = 4GB
- 工作記憶體 = 100MB
- checkpoint_completion_target = 0.9
- 自動真空 = 開啟
對此的一個側面問題：
以前我使用過
WHERE drug = ANY(ARRAY[..])，但我發現使用WHERE drug = ANY(VALUES(..))可以顯著提高速度。為什麼要有所作為？編輯 1 - 加入 VALUES 而不是 WHERE 子句
正如評論中指出的a_horse_with_no_name
WHERE，我嘗試刪除該子句並使用 aJOIN對藥物值執行查詢：詢問：
SELECT drug, reason FROM report_drugs d JOIN (VALUES (9557), (17848), (17880), (18223), (18550), (19020), (19084), (19234), (21295), (21742), (23085), (26017), (27016), (29317), (33566), (35818), (37394), (39971), (41505), (42162), (44000), (45168), (47386), (48848), (51472), (51570), (51802), (52489), (52848), (53663), (54591), (55506), (55922), (57209), (57671), (59311), (62022), (62532), (63485), (64134), (66236), (67394), (67586), (68134), (68934), (70035), (70589), (70896), (73466), (75931), (78686), (78985), (79217), (83294), (83619), (84964), (85831), (88330), (89998), (90440), (91171), (91698), (91886), (91887), (93219), (93766), (94009), (96341), (101475), (104623), (104973), (105216), (105496), (106428), (110412), (119567), (121154)) as x(d) on x.d = d.drug;計劃（根據 jjanes 的
analyze要求*）*：buffersNested Loop (cost=0.56..83531.04 rows=24164 width=26) (actual time=1.003..6927.080 rows=264610 loops=1) Buffers: shared hit=12514 read=111251 -> Values Scan on "*VALUES*" (cost=0.00..0.96 rows=77 width=4) (actual time=0.000..0.059 rows=77 loops=1) -> Index Scan using report_drugs_drug_idx on report_drugs d (cost=0.56..1081.67 rows=314 width=26) (actual time=0.217..89.551 rows=3436 loops=77) Index Cond: (drug = "*VALUES*".column1) Buffers: shared hit=12514 read=111251 Planning time: 7.616 ms Execution time: 6936.466 ms但是，這似乎沒有任何效果。雖然查詢計劃發生了一些變化，但執行時間大致相同，查詢仍然很慢。
編輯 2 - 在臨時表上加入而不是在 VALUES 上加入
按照Lennart的建議，我嘗試在單個事務中創建一個臨時表，用藥物值填充它並加入反對。雖然我獲得了大約 2 秒，但查詢仍然非常慢，只有 5 秒多一點。
查詢計劃已從 a 更改為
nested loopahash join，現在正在sequential scan對report_drugs錶執行 a。這可能是某種缺失的索引（表中的drug列report_drugs確實有索引……）？Hash Join (cost=67.38..693627.71 rows=800224 width=26) (actual time=0.711..4999.222 rows=264610 loops=1) Hash Cond: (d.drug = t.drug) -> Seq Scan on report_drugs d (cost=0.00..560537.16 rows=33338916 width=26) (actual time=0.410..3144.117 rows=33338915 loops=1) -> Hash (cost=35.50..35.50 rows=2550 width=4) (actual time=0.012..0.012 rows=77 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 35kB -> Seq Scan on t (cost=0.00..35.50 rows=2550 width=4) (actual time=0.002..0.005 rows=77 loops=1) Planning time: 7.030 ms Execution time: 5005.621 ms
您只是在與值列表進行比較，在這種情況下使用 IN 會更簡單：
SELECT drug, reason FROM drugs WHERE drug IN (9557,17848,17880,18223,18550);或者，如果您仍然使用 ANY，則使用數組文字會產生與 IN 相同的查詢計劃：
SELECT drug, reason FROM drugs WHERE drug = ANY ('{9557,17848,17880,18223,18550}');我試過這是一個較小的測試表，Postgres 能夠使用 IN 和 ANY 對帶有數組文字的查詢版本進行索引掃描，但不能對使用帶有 VALUES 的 ANY 的查詢進行索引掃描。
生成的計劃類似於以下內容（但我的測試表和數據有些不同）：
Index Scan using test_data_id_idx on test_data (cost=0.43..57.43 rows=12 width=8) (actual time=0.014..0.028 rows=12 loops=1) Index Cond: (id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12}'::integer[]))這應該比您顯示的查詢計劃快得多，因為它掃描索引一次，而您的計劃循環次數與該 WHERE 子句中的藥物一樣多。
您是否嘗試過使用連接重寫？就像是：
SELECT d.drug, d.reason FROM drugs d JOIN (VALUES (9557), (17848), (17880), (18223), (18550), (19020) , (19084), (19234), (21295), (21742), (23085), (26017) , ... ) as T(drug) ON d.drug = T.drug;作為旁注，您的某些索引似乎是多餘的。
編輯：使用臨時表
您可能還想嘗試使用臨時表而不是虛擬表。在交易中：
CREATE TABLE T (drug int not null primary key) ON COMMIT DROP; INSERT INTO T(drug) VALUES (9557), (17848), (17880), (18223), (18550), ...; SELECT d.drug, d.reason FROM drugs d JOIN T ON d.drug = T.drug;