我正在使用 PostgreSQL 13.4。
我想執行以下查詢以接收一組中每個廣告系列的最新效果的日期：
SELECT DISTINCT ON (campaignid)
campaignid,
created
FROM
effects
WHERE
campaignid IN(1, 2, 3) -- the condition. may include different values
ORDER BY
campaignid,
created DESC;
我在那個表上也有幾個索引：
CREATE INDEX effects_campaignid_created_desc_idx ON effects (campaignid, created DESC);
CREATE INDEX effects_created_idx ON effects (created);
通常當我執行查詢時，effects_campaignid_created_desc_idx會使用索引。這是執行計劃：
Unique (cost=26172.58..26406.28 rows=5 width=16) (actual time=710.816..712.315 rows=2 loops=1)
Buffers: shared hit=2480 read=2792
-> Sort (cost=26172.58..26289.43 rows=46739 width=16) (actual time=710.814..711.355 rows=12200 loops=1)
Sort Key: campaignid, created DESC
Sort Method: quicksort Memory: 956kB
Buffers: shared hit=2480 read=2792
-> Index Only Scan using effects_campaign_created_desc_idx on effects (cost=0.57..22547.42 rows=46739 width=16) (actual time=0.954..706.329 rows=12200 loops=1)
" Index Cond: (campaignid = ANY ('{1,2,3}'::bigint[]))"
Heap Fetches: 9079
Buffers: shared hit=2474 read=2792
Planning:
Buffers: shared hit=145 read=14 dirtied=1
Planning Time: 0.682 ms
Execution Time: 712.417 ms
然而：
- 有一個活動的效果比任何其他效果都多（1000 倍）。只要活動 ID 7 在請求的結果集中，查詢計劃器就會選擇全表掃描。執行計劃（不，
analyze因為它永遠運行）：
Unique (cost=27736480.20..28359558.40 rows=5 width=16)
-> Sort (cost=27736480.20..28048019.30 rows=124615640 width=16)
Sort Key: campaignid, created DESC
-> Seq Scan on effects (cost=0.00..7329244.30 rows=124615640 width=16)
" Filter: (campaignid = ANY ('{1,2,3,7}'::bigint[]))"
JIT:
Functions: 5
Options: Inlining true, Optimization true, Expressions true, Deforming true
- 當我將條件更改為
WHERE campaignid = 7查詢計劃程式時，再次更改行為，這次選擇對索引進行反向掃描effects_created_idx，這同樣效率不高。這是執行計劃：
Unique (cost=0.57..8071964.32 rows=5 width=16)
-> Index Scan Backward using effects_created_idx on effects (cost=0.57..8071964.32 rows=124568901 width=16)
Filter: (campaignid = 7)
Planning:
Buffers: shared hit=5
JIT:
Functions: 4
Options: Inlining true, Optimization true, Expressions true, Deforming true
I understand that PostgreSQL 'knows' that most of the effects have campaignid=7, for this reason it chooses to not focus on using the index on campaignid and chooses another index instead or a sequential scan.
Is there a way to hint / convince PostgreSQL to use the more efficient index effects_campaignid_created_desc_idx for these queries, regardless of which campaign I choose to get results for?
uj5u.com熱心網友回復：
為了有效地做到這一點，PostgreSQL 可能需要跳過掃描。但它不知道如何做其中之一。（人們正在努力實作它，但即使完成了，我也不知道它是否適用于 DISTINCT ON）。
在它自動作業之前，您可以使用 LATERAL 和 LIMIT 獲得有效的實作。
select * from
(values(1),(2),(3),(7)) f(campaignid)
cross join lateral
(select created from effects where f.campaignid=campaignid order by created desc limit 1)foo;
uj5u.com熱心網友回復：
嘗試替換campaignid IN(1, 2, 3) 為campaignid = ANY(1, 2, 3). 這使我的幾個查詢更快。
但是，我已經campaignid IN (SELECT UNNEST($1::TEXT[])) 替換了它，campaignid = ANY($1)因此如果您的查詢中的值是常量，則在您的情況下可能會有所不同。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/326885.html