为什么PostgreSQL在索引列上执行顺序扫描？

Question

非常简单的例子 - 一个表、一个索引、一个查询：

CREATE TABLE book
(
  id bigserial NOT NULL,
  "year" integer,
  -- other columns...
);

CREATE INDEX book_year_idx ON book (year)

EXPLAIN
 SELECT *
   FROM book b
  WHERE b.year > 2009

给了我：

Seq Scan on book b  (cost=0.00..25663.80 rows=105425 width=622)
  Filter: (year > 2009)

为什么它没有执行索引扫描？我错过了什么吗？

- Alex Vayda

Answer 1

如果SELECT返回的行数超过表中所有行的5-10％左右，那么顺序扫描比索引扫描快得多。

这是因为索引扫描对于每一行都需要多个IO操作（在索引中查找行，然后从堆中检索行）。而顺序扫描仅需要每行一个IO操作，甚至更少，因为磁盘上的块（页）包含多行，因此可以通过单个IO操作获取多行。

顺带一提：其他DBMS也是如此 - 除了某些优化措施，例如“仅索引扫描”（但对于SELECT *，这种DBMS几乎不可能进行“仅索引扫描”）。

- user330315

---

有趣，这为我解释了很多事情 :) 确实，当我选择年份>2010时，它进行索引扫描。 谢谢！ - Alex Vayda

此外，顺序扫描可以一次请求堆中的多个页面，并要求内核在当前页面上工作时获取下一个页面块 - 索引扫描每次只获取一个页面。(位图扫描在两者之间做出妥协，通常在查询计划中出现，用于对索引扫描不够选择性但又不足以进行完整表扫描的查询。) - araqnid

有趣的问题是，数据库如何在不先执行查询的情况下知道它将返回多少行？它是否存储有关不同值数量与表大小之间关系的统计数据？ - Laurent Grégoire

@LaurentGrégoire：是的，数据库存储关于行数和值分布的统计信息。详细信息请参阅手册：https://www.postgresql.org/docs/current/static/planner-stats.html - user330315

@a_horse_with_no_name 在这种情况下，它取决于连接表的 where 条件：如果我在特定的表上使用 where，我想要使用索引，那么它会使用索引，速度会更快。否则，如果我使用原始表上的列而不是连接的列/表，顺便说一下，后者有更多的行，那么它就不会使用索引，速度会慢得多。对我来说，这似乎是一个规划器上的错误... - brauliobo

Answer 2

您是否对表/数据库进行了分析？那么统计数据呢？当有许多记录年份>2009时，顺序扫描可能比索引扫描更快。

- Frank Heikens

Answer 3

@a_horse_with_no_name 解释得很清楚。另外，如果您真的想使用索引扫描，通常应在where子句中使用有界范围。例如 - year>2019并且year<2020。

很多时候，一个表上的统计数据没有被更新，而且由于约束条件，可能无法进行更新。在这种情况下，优化器将不知道在year>2019中应该选择多少行。因此，它会选择顺序扫描来代替完全了解。有界分区大多数情况下都可以解决这个问题。

- Shitij Goyal

Answer 4

在索引扫描中，读取头部从一行跳到另一行的速度比读取下一个物理块（在顺序扫描中）慢1000倍。

因此，如果要检索的记录数量乘以1000小于总记录数，则索引扫描的性能更好。

- Gaurav Neema