enable_seqscan

• 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

给定以下（简单的）表

postgres=# CREATE TABLE foo (id INT) ;
CREATE TABLE

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

postgres=# ANALYZE foo;
ANALYZE

具有低选择性的查询的典型计划将导致顺序扫描

postgres=# EXPLAIN ANALYZE SELECT * FROM foo WHERE id %2 = 1;
                                          QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..2.50 rows=1 width=4) (actual time=0.029..0.062 rows=50 loops=1)
   Filter: ((id % 2) = 1)
   Rows Removed by Filter: 50
 Planning Time: 0.278 ms
 Execution Time: 0.105 ms
(5 rows)

将 enable_seqscan 设置为 off 不会阻止顺序扫描，但是成本估算将被人为地“膨胀”，以确保如果存在另一种可用计划类型，则会使用该计划。

postgres=# EXPLAIN ANALYZE SELECT * FROM foo WHERE id %2 = 1;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=10000000000.00..10000000002.50 rows=1 width=4) (actual time=0.049..0.128 rows=50 loops=1)
   Filter: ((id % 2) = 1)
   Rows Removed by Filter: 50
 Planning Time: 0.112 ms
 Execution Time: 0.180 ms
(5 rows)

但是，如果出现可用的替代扫描方法（例如，通过添加索引），则将使用该方法

postgres=# CREATE INDEX foo_id ON foo(id);
CREATE INDEX

postgres=# ANALYZE foo;
ANALYZE

postgres=# EXPLAIN ANALYZE SELECT * FROM foo WHERE id %2 = 1;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Index Only Scan using foo_id on foo  (cost=0.14..14.14 rows=1 width=4) (actual time=0.179..0.279 rows=50 loops=1)
   Filter: ((id % 2) = 1)
   Rows Removed by Filter: 50
   Heap Fetches: 100
 Planning Time: 0.181 ms
 Execution Time: 0.378 ms
(6 rows)

即使规划器通常可能不使用它

postgres=# SET enable_seqscan = on;
SET

postgres=# EXPLAIN ANALYZE SELECT * FROM foo WHERE id %2 = 1;
                                          QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..2.50 rows=1 width=4) (actual time=0.038..0.072 rows=50 loops=1)
   Filter: ((id % 2) = 1)
   Rows Removed by Filter: 50
 Planning Time: 0.152 ms
 Execution Time: 0.117 ms
(5 rows)

• PostgreSQL 文档： enable_seqscan