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

本文介绍了为什么PostgreSQL对索引列执行顺序扫描？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

Very simple example - one table, one index, one query:

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)

为什么它不执行索引扫描？ 我缺少什么？

Why it does NOT perform index scan instead? What am I missing?

推荐答案

如果SELECT返回超过大约5-10％的所有行表，顺序扫描比索引扫描快得多。

If the SELECT returns more than approximately 5-10% of all rows in the table, a sequential scan is much faster than an index scan.

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

This is because an index scan requires several IO operations for each row (look up the row in the index, then retrieve the row from the heap). Whereas a sequential scan only requires a single IO for each row - or even less because a block (page) on the disk contains more than one row, so more than one row can be fetched with a single IO operation.

Btw：对于其他DBMS也是如此 - 一些优化仅作为仅索引扫描（但对于SELECT *，这样的DBMS极不可能仅用于索引）扫描）

Btw: this is true for other DBMS as well - some optimizations as "index only scans" taken aside (but for a SELECT * it's highly unlikely such a DBMS would go for an "index only scan")