为什么 PostgreSQL 对索引列执行顺序扫描? [英] Why does PostgreSQL perform sequential scan on indexed column?

问题描述

非常简单的例子——一张表，一个索引，一个查询:

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.

顺便说一句:对于其他 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")

这篇关于为什么 PostgreSQL 对索引列执行顺序扫描?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！