Postgres：如何強制索引使用以查看最新費率？

我們將貨幣匯率導入 DB：

CREATE TABLE currency_rate (
   id int8 NOT NULL,
   date date NOT NULL,
   currency varchar(3) NOT NULL,
   rate numeric(12,6) NOT NULL,
   CONSTRAINT currency_rate_pk PRIMARY KEY (id)
);

ALTER TABLE currency_rate add constraint currency_rate_un UNIQUE (currency, date);

但實際上我們只需要最新的可用匯率即可。

用 sort 和 寫 CTE 很麻煩distinct on (currency)：

with cr as (
 select distinct on (currency) currency, rate from currency_rate
 order by currency, date)
select
 ...,
 sum((nd.original_amount - nd.new_amount)*cr.rate) as amount
from notification_data nd
join cr on cr.currency = nd.currency
...

查詢具有以下很好的執行計劃：

 CTE cr
   ->  Result  (cost=0.28..69.66 rows=13 width=16)
         ->  Unique  (cost=0.28..69.66 rows=13 width=16)
               ->  Index Scan using currency_rate_un on currency_rate  (cost=0.28..67.17 rows=995 width=16)
 ...
             ->  Hash Join  (cost=1029.26..57129.68 rows=18 width=60)
                   Hash Cond: ((nd.currency)::text = (cr.currency)::text)

我創建了視圖：

CREATE OR REPLACE VIEW latest_currency_rate AS
SELECT
 DISTINCT ON (currency) currency, rate, date
FROM currency_rate
ORDER BY currency, date DESC;

但數據庫優化器不使用來自的索引currency_rate_un：

explain select * from latest_currency_rate;

Unique  (cost=60.83..65.38 rows=12 width=16)
 ->  Sort  (cost=60.83..63.10 rows=910 width=16)
       Sort Key: currency_rate.currency, currency_rate.date DESC
       ->  Seq Scan on currency_rate  (cost=0.00..16.10 rows=910 width=16)

甚至對於：

explain select * from latest_currency_rate where currency = 'USD';

Unique  (cost=16.87..17.13 rows=12 width=16)
 ->  Sort  (cost=16.87..17.13 rows=104 width=16)
       Sort Key: currency_rate.date DESC
       ->  Bitmap Heap Scan on currency_rate  (cost=5.08..13.38 rows=104 width=16)
             Recheck Cond: ((currency)::text = 'USD'::text)
             ->  Bitmap Index Scan on currency_rate_un  (cost=0.00..5.06 rows=104 width=0)
                   Index Cond: ((currency)::text = 'USD'::text)

新視圖與原始查詢的集成給出：

explain select
 sum((nd.original_amount - nd.new_amount)*cr.rate) as amount
from notification_data nd
join latest_currency_rate cr on cr.currency = nd.currency
...

...
->  Hash  (cost=73.54..73.54 rows=13 width=12)
      ->  Subquery Scan on cr  (cost=68.37..73.54 rows=13 width=12)
            ->  Unique  (cost=68.37..73.41 rows=13 width=16)
                  ->  Sort  (cost=68.37..70.89 rows=1008 width=16)
                        Sort Key: currency_rate.currency, currency_rate.date DESC
                        ->  Seq Scan on currency_rate  (cost=0.00..18.08 rows=1008 width=16)
...

現在我很困惑。為什麼原始 CTE 查詢使用Index Scan和視圖不使用相同的索引？

我應該用一些替代技巧（而不是distinct on）重寫視圖嗎？

我正在考慮materialized view避免順序掃描…

你的觀點給出了正確的答案，而你的 CTE 給出了錯誤的答案，使用最舊的日期，而不是最新的日期。

如果您想使用索引掃描（儘管在我手中它實際上並沒有對性能產生影響），DESC請為兩個 ORDER BY 列指定，或者為(currency, date DESC).

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