PostgreSQL简单排序查询为何选顺序扫描？索引扫描成本过高原因

当执行查询 select b.* from body as b order by b.orbital_eccentricity desc; 时，禁用顺序扫描（set enable_seqscan = off;）后会走索引反向扫描，但开启顺序扫描时，PostgreSQL默认选择了顺序扫描+并行排序的执行计划，且索引扫描的估算成本远高于顺序扫描。

执行计划对比 #

禁用顺序扫描时的执行计划 #

set enable_seqscan = off;
explain select b.* from body as b order by b.orbital_eccentricity desc;

Index Scan Backward using body_orbital_eccentricity_idx on body b  (cost=0.57..1912033501.86 rows=486411520 width=110)

开启顺序扫描时的执行计划 #

set enable_seqscan = on;
explain select b.* from body as b order by b.orbital_eccentricity desc;

Gather Merge  (cost=97808148.33..145101459.15 rows=405342934 width=110)
  Workers Planned: 2
  ->  Sort  (cost=97807148.31..98313826.97 rows=202671467 width=110)
        Sort Key: orbital_eccentricity DESC
        ->  Parallel Seq Scan on body b  (cost=0.00..22738694.67 rows=202671467 width=110)

表结构定义 #

-- public.body definition

CREATE TABLE public.body (
    id64 int8 NOT NULL,
    body_id int8 NULL,
    "name" varchar NULL,
    type_field varchar NULL,
    orbital_period float8 NULL,
    semi_major_axis float8 NULL,
    orbital_eccentricity float8 NULL,
    arg_of_periapsis float8 NULL,
    mean_anomaly float8 NULL,
    ascending_node float8 NULL,
    update_time timestamp NULL,
    system_id64 int8 NOT NULL,
    CONSTRAINT body_pk PRIMARY KEY (id64)
);
CREATE INDEX body_orbital_eccentricity_idx ON public.body USING btree (orbital_eccentricity);

-- public.body foreign keys

ALTER TABLE public.body ADD CONSTRAINT body_system_fk FOREIGN KEY (system_id64) REFERENCES public."system"(id64);

问题解答 #

1. 为何PostgreSQL默认选择顺序扫描而非索引扫描？ #

这个查询需要返回全表所有行并按orbital_eccentricity排序：

• 索引扫描虽然能直接获取有序的orbital_eccentricity数据，但索引只存储了排序字段和主键id64，要获取所有列必须执行回表操作——每一条索引项都要通过主键去主表读取完整行。
• 你的表有近5亿行，回表会产生近5亿次随机IO，而随机IO的成本在PostgreSQL的估算模型里远高于顺序IO（默认random_page_cost=4，seq_page_cost=1）。
• 顺序扫描则是通过并行方式一次性读取全表数据，再在内存中排序。虽然排序需要消耗CPU和内存，但相比近5亿次随机回表的总成本，顺序扫描+排序的估算成本更低，因此优化器选择了后者。

2. 为何使用索引的查询成本上限高达19.1亿？ #

这个19.1亿的成本是PostgreSQL对索引遍历+全量回表的总成本估算：

• 首先要遍历整个body_orbital_eccentricity_idx索引（近5亿条索引项），这部分成本较低；
• 关键成本来自回表：每一条索引项都要通过主键去主表获取完整行，PostgreSQL会按照随机IO的成本标准（random_page_cost）来计算这部分开销。近5亿次随机IO的成本累加后，直接将总成本推到了19亿级别。
• 这个数值是估算值，实际执行时如果主表数据有较多缓存，实际耗时可能没这么夸张，但优化器的成本模型就是基于这种逻辑计算的。

内容的提问来源于stack exchange，提问作者tuskiomi