random_page_cost

• 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

给定一个任意大的表，并将random_page_cost 设置为默认值4，PostgreSQL 会选择索引扫描

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

postgres=# INSERT INTO foo values(generate_series(1,10000000));
INSERT 0 10000000

postgres=# SHOW random_page_cost;
 random_page_cost 
------------------
 4
(1 row)

postgres=# EXPLAIN SELECT * FROM foo WHERE id BETWEEN 1000000 and 2000000;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Index Only Scan using foo_pkey on foo  (cost=0.43..32073.10 rows=1035633 width=4)
   Index Cond: ((id >= 1000000) AND (id <= 2000000))
(2 rows)

但是，如果将random_page_cost 设置为（一个不太可能的高）值，PostgreSQL 将确定顺序扫描将更有效（即使实际上可能并非如此）

postgres=# SET random_page_cost to 4000;
SET

postgres=# EXPLAIN SELECT * FROM foo WHERE id BETWEEN 1000000 and 2000000;
                          QUERY PLAN                          
--------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..194248.72 rows=1035633 width=4)
   Filter: ((id >= 1000000) AND (id <= 2000000))
(2 rows)

• PostgreSQL 文档： random_page_cost

• 调整 random_page_cost 及索引相关性如何影响查询计划 - 2024 年 5 月 PgAnalyze 博客文章