我有兩個表exchange_rate（10 萬行）和paid_date_t（900 萬行），結構如下。
"exchange_rate"
Column | Type | Collation | Nullable | Default
----------------------------- -------------------------- ----------- ---------- ---------
valid_from | timestamp with time zone | | |
valid_until | timestamp with time zone | | |
currency | text | | |
Indexes:
"exchange_rate_unique_valid_from_currency_key" UNIQUE, btree (valid_from, currency)
"exchange_rate_valid_from_gist_idx" gist (valid_from)
"exchange_rate_valid_from_until_currency_gist_idx" gist (valid_from, valid_until, currency)
"exchange_rate_valid_from_until_gist_idx" gist (valid_from, valid_until)
"exchange_rate_valid_until_gist_idx" gist (valid_until)
"paid_date_t"
Column | Type | Collation | Nullable | Default
------------------- ----------------------------- ----------- ---------- ---------
currency | character varying(3) | | |
paid_date | timestamp without time zone | | |
Indexes:
"paid_date_t_paid_date_idx" btree (paid_date)
我在選擇查詢下方運行并基于多個連接鍵加入這些表：
SELECT
paid_date
FROM exchange_rate erd
JOIN paid_date_t sspd
ON sspd.paid_date >= erd.valid_from AND sspd.paid_date < erd.valid_until
AND erd.currency = sspd.currency
WHERE sspd.currency != 'USD'
但是，查詢的性能低下，并且需要數小時才能執行。下面的查詢計劃顯示它使用嵌套回圈連接。
Nested Loop (cost=0.28..44498192.71 rows=701389198 width=40)
-> Seq Scan on paid_date_t sspd (cost=0.00..183612.84 rows=2557615 width=24)
Filter: ((currency)::text <> 'USD'::text)
-> Index Scan using exchange_rate_valid_from_until_currency_gist_idx on exchange_rate erd (cost=0.28..16.53 rows=80 width=36)
Index Cond: (currency = (sspd.currency)::text)
Filter: ((sspd.paid_date >= valid_from) AND (sspd.paid_date < valid_until))
我使用了不同的索引方法，但得到了相同的結果。我知道<=并且>=運營商不支持合并或散列連接。
任何想法表示贊賞。
uj5u.com熱心網友回復：
您應該創建一個較小的表，其中僅包含來自paid_date_t 的行樣本。如果每次嘗試測驗查詢都需要很長時間，則很難優化查詢。
您的 btree 索引已將經過相等性測驗的列作為第二列，這肯定效率較低。此查詢的更好的 btree 索引（如當前撰寫的那樣）將類似于(currency, valid_from, valid_until).
對于要點索引，您真的希望它位于時間范圍內，而不是范圍的單獨端點上。您可以轉換表以保存范圍型別，或構建功能索引以即時轉換它們（然后重寫查詢以使用相同的運算式）。由于時區的處理方式不同，您的表格具有不同的型別，這使情況變得復雜。索引看起來像：
create index on exchange_rate using gist (tstzrange(valid_from,valid_until), currency);
然后 ON 條件如下所示：
ON sspd.paid_date::timestamptz <@ tstzrange(erd.valid_from, erd.valid_until)
AND erd.currency = sspd.currency
將 gist 索引中的列順序與我顯示的相反可能會更快，您應該在自己的資料上嘗試兩種方式并查看。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/474662.html
標籤：PostgreSQL 加入 索引 查询优化
上一篇：加入日期表，帶有臟日期欄位