enable_seqscan

• 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