为什么Postgres在使用Distinct时没有使用索引？

Question

我有这个表格：

CREATE TABLE public.prodhistory (
  curve_id           int4 NOT NULL,
  start_prod_date    date NOT NULL,
  prod_date          date NOT NULL,
  monthly_prod_rate  float4 NOT NULL,
  eff_date           timestamp NOT NULL,
  /* Keys */
  CONSTRAINT prodhistorypk
    PRIMARY KEY (curve_id, prod_date, start_prod_date, eff_date),
  /* Foreign keys */
  CONSTRAINT prodhistory2typecurves_fk
    FOREIGN KEY (curve_id)
    REFERENCES public.typecurves(curve_id)
) WITH (
    OIDS = FALSE
  );

CREATE INDEX prodhistory_idx_curve_id01
  ON public.prodhistory
  (curve_id);

这里有大约4200万行数据。

我要执行以下查询：

SELECT DISTINCT curve_id FROM prodhistory

我希望这个过程会非常快，因为有索引。但是实际上需要270秒。于是我解释了一下，得到了如下回复：

HashAggregate  (cost=824870.03..824873.08 rows=305 width=4) (actual time=211834.018..211834.097 rows=315 loops=1)   
  Output: curve_id  
  Group Key: prodhistory.curve_id   
  ->  Seq Scan on public.prodhistory  (cost=0.00..718003.22 rows=42746722 width=4) (actual time=12.751..200826.299 rows=43218808 loops=1)   
        Output: curve_id    
Planning time: 0.115 ms 
Execution time: 211848.137 ms   

我对阅读这些计划不是很有经验，但在数据库上进行Seq Scan似乎不太好。

有什么想法吗？我有点困惑。

- Marc

---

我的意思是，curve_id 有多少个不同的值？另外，将PK设置为整个表的目的是什么？ - Lamak

请展示 explain (analyze, verbose) 的输出结果。 - user330315

@lamak - 目前有300个不同的值，可能最终会接近1000个。此外，主键是为了我可以对每一行进行版本控制 - 数据每月更新一次，但只有最近两年的“prod_date”数据会被更新。而且主键定义了表中唯一的内容，符合良好的实践。 - Marc

索引包含所有表的列 - 它只是整个表的副本。为什么您认为在这种情况下扫描索引应该比扫描表更快？索引和表包含相同的数据，它们在磁盘上的大小相同。仅在 curve_id 列上创建一个索引。 - krokodilko

空分析 prodhistory 报告无内容，也不执行任何操作。 - Marc

Answer 1

这个方案被选择，因为PostgreSQL认为它更便宜。您可以通过设置进行比较。

SET enable_seqscan=off;

然后重新运行您的EXPLAIN (ANALYZE)语句。比较两种情况下的cost和actual time，检查PostgreSQL是否正确估计。

如果您发现使用Index Scan或Index Only Scan实际上更便宜，您可以考虑调整成本参数以更好地匹配您的机器，例如降低random_page_cost或cpu_index_tuple_cost或提高cpu_tuple_cost。

- Laurenz Albe

---

使用 SET enable_seqscan=off; 确实会将查询计划更改为“仅索引扫描”，但仍然没有从具有许多重复值中获得任何好处。扫描的行数仍然比返回的行数要高得多。我希望PostgreSQL足够聪明，能够跳到下一个不同的值，而不是扫描它们所有。 - Georgi Yanchev

PostgreSQL 无法做到这一点。即使它能够做到，只有在有少量不同的值时才会有益处，因为顺序扫描通常比随机访问更快。 - Laurenz Albe

Answer 2

PostgreSQL的“仅索引扫描”并不总是像你想象的那么便宜。

原因在于需要检查每一行是否对MVCC快照可见。

这是否便宜取决于表的可见性映射。

如果强制进行仅索引扫描（根据laurenz-albe的答案）：

SET enable_seqscan=off;

然后使用以下命令运行您的查询：

EXPLAIN (ANALYZE ON, BUFFERS ON)

如果在查询计划输出中看到"heap fetches"，这意味着正在访问表的实际行数据，而不仅仅是索引。

Index Only Scan using my_index on my_table  (cost=0.42..17792.01 rows=595195 width=20) (actual time=37.942..2330.737 rows=539105 loops=1)
    Heap Fetches: 234180

官方文档在这里进行了描述：

https://www.postgresql.org/docs/current/indexes-index-only-scans.html

您可能可以通过更改表格更新方式或调整自动清理设置来解决此问题。

- Antony Perkov