我有一個 postgres 13.3 表，如下所示：
CREATE TABLE public.enrollments (
id bigint NOT NULL,
portfolio_id bigint NOT NULL,
consumer_id character varying(255) NOT NULL,
identity_id character varying(255) NOT NULL,
deleted_at timestamp(0) without time zone,
batch_replace boolean DEFAULT false NOT NULL
);
CREATE UNIQUE INDEX enrollments_portfolio_id_consumer_id_index ON public.enrollments
USING btree (portfolio_id, consumer_id) WHERE (deleted_at IS NULL);
每個投資組合通常包含數百萬個注冊。我的客戶通常會定期向我發送一個批處理檔案，其中包含他們所有的注冊資訊，因此我必須使 db 與此檔案匹配。我嘗試一次讀取大約 1000 個資料塊，然后通過如下查詢來檢查注冊是否預先存在：
SELECT * FROM enrollments WHERE deleted_at IS NULL AND portfolio_id = 1
AND consumer_id = ANY(ARRAY["C1", "C2", ..., "C1000"])
似乎對于新的投資組合，它不使用唯一的部分索引，因此此查詢最多可能需要 30 秒。當投資組合中已經有幾百萬注冊人時，該指數似乎有效并且需要大約 20 毫秒。我不得不將 sql 更改為一次只查詢一個注冊，這大約需要 1 秒/1000 秒。這并不理想，因為完成一個檔案最多可能需要一天的時間，但至少它會完成。
有誰知道在選擇中使用許多 consumer_id 時我可以做些什么來使唯一的部分索引一致使用？
下面是一些解釋輸出。冗長的查詢花費了 4 秒多一點，隨著越來越多的注冊被插入到投資組合中，這會增加到至少 30 秒，直到達到某個點并下降到大約 20 毫秒
Existing enrollments in this portfolio: 78140485
Index Scan using enrollments_portfolio_id_consumer_id_index on enrollments e0 (cost=0.70..8637.14 rows=1344 width=75) (actual time=3.529..37.827 rows=1000 loops=1)
Index Cond: ((portfolio_id = '59031'::bigint) AND ((consumer_id)::text = ANY ('{C1,C2,...,C1000}'::text[])))
I/O Timings: read=27.280
Planning Time: 0.477 ms
Execution Time: 37.914 ms
Benchmark time: 20 ms
Existing enrollments in this portfolio: 136000
Index Scan using enrollments_portfolio_id_consumer_id_index on enrollments e0 (cost=0.70..8.87 rows=1 width=75) (actual time=76.615..4354.081 rows=1000 loops=1)
Index Cond: (portfolio_id = '59028'::bigint)
Filter: ((consumer_id)::text = ANY ('{C1,C2,...,C1000}'::text[]))
Rows Removed by Filter: 135000
Planning Time: 1.188 ms
Execution Time: 4354.341 ms
Benchmark time: 4398 ms
uj5u.com熱心網友回復：
這里實際上很慢的事情=ANY是通過回圈遍歷陣列的 1000 個成員并測驗每個成員來實作的，然后對它需要檢查的 136000 行中的每一行執行此操作。這是很多回圈（但在我手中不值 4 秒，對我來說“只有”1.5 秒）。更糟糕的是，計劃者沒有預料到=ANY執行如此糟糕，因此認為沒有理由選擇另一個計劃來避免它。
v14 將通過使用哈希表來解決這個問題=ANY，因此它不再那么慢。
如果您不能/不想升級到 v14，您可以通過加入 VALUES 串列來重寫查詢，而不是使用 =ANY
SELECT * FROM enrollments join (VALUES ('C1'),...,('C1000')) f(c) on c=consumer_id
WHERE deleted_at IS NULL AND portfolio_id = 1
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/313930.html
標籤：sql 数据库 PostgreSQL 表现 索引