如何在 Postgres 上對傾斜數據集進行更高效的查詢

我有一個看似簡單的查詢，不幸的是非常慢。

我相信我知道為什麼它很慢，但不知道如何讓它變快，所以我想看看如何改進它。

我們將有問題的表稱為“PriceHistory”，它跟踪“productId”的“price”（數字），有數百萬行，每個 productId 有數千個條目。

productId 上有一個 btree 索引，price 上有一個索引。

至關重要的是（我認為！）隨​​著新的 productId 事物的創建，它們的價格數據保持在一起，因此在到達與給定 productId 相關的第一行之前，可能有數百萬行與不同的 productId 相關。

超慢查詢：

EXPLAIN ANALYZE SELECT min(price) FROM PriceHistory WHERE productId = 'someId'

Result (cost=555.25..555.26 rows=1 width=32) (actual time=100084.212..100084.213 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.43..555.25 rows=1 width=6) (actual time=100084.209..100084.209 rows=1 loops=1)
-> Index Scan using "PriceHistory_btree_price" on "PriceHistory" (cost=0.43..2492270.00 rows=4492 width=6) (actual time=100084.207..100084.208 rows=1 loops=1)
Index Cond: (price IS NOT NULL)
Filter: ((productId)::text = 'someId'::text)
Rows Removed by Filter: 1140612
Planning Time: 0.124 ms
Execution Time: 100084.230 ms

強制使用更好索引的快速等效查詢

> EXPLAIN ANALYZE WITH x as (SELECT price_number FROM tradingcards_live."custom$0card_prices" where "card_custom_card" = '1348695171700984260__LOOKUP__1587446850514x224832321163624450') SELECT min(price_number) from x
Aggregate (cost=14964.82..14964.83 rows=1 width=32) (actual time=1584.004..1584.005 rows=1 loops=1)
-> Index Scan using "PriceHistory_btree_productid" on "PriceHistory" (cost=0.56..14953.58 rows=4493 width=6) (actual time=0.909..1582.147 rows=4674 loops=1)
Index Cond: ((productId)::text = 'someId'::text)
Planning Time: 0.149 ms
Execution Time: 1584.027 ms

我在這裡的理解是，基本統計數據告訴 postgres 價格的數字索引期望它在 productId 上“更快”達到匹配，因為有多少行匹配 someId （在這種情況下，4673 - 所以〜在假設均勻分佈的前 1000 行中5M 行），這可能會導致 postgres 認為掃描價格值直到第一個匹配項而不是匹配正確的 productId 值並在記憶體中進行聚合更便宜。

這個假設是否正確，我們如何才能使初始查詢自動選擇更好的索引，給定數據傾斜 - “匹配列 X 的值被捆綁在一起，根本不均勻分佈，並且足夠高以至於掃描將先遍歷很多行”？

您對問題的評估是正確的：PostgreSQL 不知道滿足條件的許多行儲存在磁碟的哪個位置。

我能想到的唯一安全的方法是修改查詢，使 PostgreSQL 不能使用索引：

SELECT min(price + 0)
FROM PriceHistory
WHERE productId = 'someId';

但是當然，這也會阻止索引在更快的計劃中使用……

你是對的，計劃者不知道行是如何排列的，所以選擇了錯誤的索引。

正如 Laurenz 所建議的那樣，您可以強制規劃器使用兩個次優索引中的更好的一個。但是最好只提供最佳索引，這將在(productid, price)

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