PostgreSQL未使用枚举索引过滤数据问题咨询

问题背景 #

表结构如下：

create table my_table
(
    id               bigint generated always as identity
        primary key,
    match_request_id text             not null,
    asset_type       asset_type       not null,
    created_at       timestamp with time zone default now(),
    updated_at       timestamp with time zone
);

create index my_table_asset_type_type_idx
    on mydb.my_table (asset_type);

枚举类型asset_type的取值为{B_GEO,B_LOCATION}，表中约5亿行数据，其中80%属于B_GEO。执行以下查询时，PostgreSQL选择了全表扫描而非索引扫描，耗时极长：

EXPLAIN analyse
select * from mydb.my_table where asset_type= 'B_GEO';

查询计划结果：

Seq Scan on my_table  (cost=0.00..15622383.34 rows=399039159 width=125) (actual time=0.012..157888.342 rows=398513460 loops=1)
  Filter: (asset_type = 'B_GEO'::asset_type)
  Rows Removed by Filter: 94628941
Planning Time: 0.073 ms
Execution Time: 176045.579 ms

为什么不使用索引？ #

PostgreSQL的查询优化器会根据数据分布估算查询返回的行数占比：当查询需要返回超过表总量20%-30%的数据时，全表扫描（Seq Scan）通常比索引扫描更高效。原因在于：

• 索引扫描需要先遍历索引找到匹配的行指针，再回表读取完整数据，这个过程涉及大量随机IO，磁盘开销更高；
• 全表扫描是顺序读取磁盘数据，连续IO的效率远高于随机IO，尤其当大部分数据都需要被返回时，避免了回表的额外开销。

你的场景中B_GEO占比80%，优化器判断全表扫描是更优选择，这个决策是合理的。

优化方案 #

1. 避免SELECT *，使用覆盖索引 #

如果你的查询不需要返回所有字段，只需要部分列，可以创建覆盖索引，将需要的字段包含在索引中，这样查询时不需要回表，直接从索引获取数据，索引扫描的效率会提升：

-- 示例：包含id、match_request_id字段，根据实际需求调整
CREATE INDEX idx_my_table_asset_type_include 
ON mydb.my_table (asset_type) 
INCLUDE (id, match_request_id);

2. 测试索引扫描性能（临时调整） #

可以临时关闭全表扫描开关，强制使用索引扫描，对比两种方式的实际耗时：

SET enable_seqscan = off;
EXPLAIN ANALYSE select * from mydb.my_table where asset_type= 'B_GEO';
-- 测试完成后记得恢复默认设置
SET enable_seqscan = on;

注意：不要长期全局关闭enable_seqscan，这会影响其他查询的优化器决策，仅用于测试验证。

3. 按asset_type分区 #

对于5亿行的大表，按枚举值做LIST分区是更彻底的优化方案：

-- 创建分区表
CREATE TABLE my_table_partitioned (
    id               bigint generated always as identity,
    match_request_id text             not null,
    asset_type       asset_type       not null,
    created_at       timestamp with time zone default now(),
    updated_at       timestamp with time zone
) PARTITION BY LIST (asset_type);

-- 创建B_GEO分区
CREATE TABLE my_table_geo PARTITION OF my_table_partitioned
FOR VALUES IN ('B_GEO');

-- 创建B_LOCATION分区
CREATE TABLE my_table_location PARTITION OF my_table_partitioned
FOR VALUES IN ('B_LOCATION');

-- 迁移数据到分区表（根据实际情况选择合适的迁移方式）
INSERT INTO my_table_partitioned SELECT * FROM my_table;

-- 在分区上创建索引（可选，分区查询本身已经是扫描对应分区）
CREATE INDEX idx_my_table_geo_asset_type ON my_table_geo (asset_type);

分区后查询B_GEO时，PostgreSQL会直接扫描对应的分区，避免扫描整个大表，性能会显著提升。

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