为什么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.
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")
这篇关于为什么PostgreSQL对索引列执行顺序扫描?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!