enable_indexscan

• 19
• 18
• 17
• 16
• 15
• 14
• 13
• 12
• 11
• 10
• 9.6
• 9.5
• 9.4
• 9.3
• 9.2
• 9.1
• 9.0
• 8.4
• 8.3
• 8.2

enable_indexscan 的基本用法示例

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;

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_indexonlyscan 已被禁用，以强制执行索引扫描。

将 enable_indexscan 设置为 off

postgres=# EXPLAIN SELECT * FROM foo WHERE id BETWEEN 99 AND 3024;
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=66.28..155.17 rows=2926 width=4)
   Recheck Cond: ((id >= 99) AND (id <= 3024))
   ->  Bitmap Index Scan on foo_pkey  (cost=0.00..65.55 rows=2926 width=0)
         Index Cond: ((id >= 99) AND (id <= 3024))
(4 rows)

• PostgreSQL 文档： enable_indexscan