enable_indexonlyscan

• 19
• 18
• 17
• 16
• 15
• 14
• 13
• 12
• 11
• 10
• 9.6
• 9.5
• 9.4
• 9.3
• 9.2

enable_indexonlyscan 的基本用法示例

postgres=# CREATE TABLE foo (id INT NOT NULL PRIMARY KEY);
CREATE TABLE

postgres=# INSERT INTO foo VALUES(generate_series(1,10000));
INSERT 0 10000

postgres=# ANALYZE foo;
ANALYZE

postgres=# EXPLAIN SELECT * FROM foo WHERE id BETWEEN 99 AND 3024;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Index Only Scan using foo_pkey on foo  (cost=0.29..111.81 rows=2926 width=4)
   Index Cond: ((id >= 99) AND (id <= 3024))
(2 rows)

将 enable_indexonlyscan 设置为 off

postgres=# SET enable_indexonlyscan to off;
SET
postgres=# EXPLAIN SELECT * FROM foo WHERE id BETWEEN 99 AND 3024;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Index Scan using foo_pkey on foo  (cost=0.29..111.81 rows=2926 width=4)
   Index Cond: ((id >= 99) AND (id <= 3024))
(2 rows)

此处优化器已回退到索引扫描。有关当索引扫描也禁用时的相同查询的示例输出，请参见 enable_indexscan。

• PostgreSQL 文档: enable_indexonlyscan