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避免顺序扫描...