PostgreSQL选择错误索引致查询性能极差，如何解决？

问题场景 #

我创建了两个结构、索引、数据量完全相同但数据分布不同的PostgreSQL表logs1和logs2：

1. 创建两张相同表：

CREATE TABLE logs1 (
    user_id int4 NOT NULL,
    create_time timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE logs2 (
    user_id int4 NOT NULL,
    create_time timestamptz NOT NULL DEFAULT now()
);

2. 向logs1插入2亿条数据，100个用户各200万条：

INSERT INTO public.logs1 (user_id) (select 1 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 2 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 3 from generate_series(1,2000000));
--...
INSERT INTO public.logs1
SELECT user_id
     , now()+make_interval(secs => user_id)
FROM generate_series(4,100) AS user_id
CROSS JOIN LATERAL generate_series(1,2e6) AS duplicate_count;

3. 向logs2插入2亿条数据，但少数用户数据占比极高：

INSERT INTO public.logs2(user_id) select 1 from generate_series(1,95802970);
INSERT INTO public.logs2(user_id) select 2 from generate_series(1,10561980);
INSERT INTO public.logs2(user_id) select 3 from generate_series(1,17014900);
--...
INSERT INTO public.logs2 
SELECT ordinality+3
     , now()+make_interval(secs => ordinality)
FROM unnest(ARRAY[/*95802970,10561980,17014900,*/14987000,14701010,13137950,7180130,6930550,4260890,3702960,3215300,2648260,1197320,802200,709840,661650,628120,590780,433570,291460,195000,158450,55980,34210,21660,17400,9500,7850,7450,4910,4860,3150,2760,2620,2160,1810,1630,1410,1090,1050,830,610,560,540,500,490,330,240,210,160,130,110,100,100,90,90,90,80,80,70,70,70,60,40,40,40,30,30,30,30,30,20,20,20,20,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10])
WITH ORDINALITY AS _(val,ordinality)
CROSS JOIN LATERAL generate_series(1,val);

4. 创建相同的索引：

CREATE INDEX logs1_user_id_idx ON ONLY logs1 USING btree (user_id);
CREATE INDEX logs1_create_time_idx ON ONLY logs1 USING btree (create_time);
CREATE INDEX logs1_user_id_create_time_idx ON ONLY logs1 USING btree (user_id, create_time);

CREATE INDEX logs2_user_id_idx ON ONLY logs2 USING btree (user_id);
CREATE INDEX logs2_create_time_idx ON ONLY logs2 USING btree (create_time);
CREATE INDEX logs2_user_id_create_time_idx ON ONLY logs2 USING btree (user_id, create_time);

5. 执行统计更新：

analyze logs1;
analyze logs2;

执行单用户查询时，两张表都正确使用logs*_user_id_create_time_idx联合索引，查询快速完成。但执行多用户IN查询：

EXPLAIN ANALYZE
SELECT *
FROM logs1 -- logs2
WHERE user_id IN (1001, 1002)
ORDER BY create_time  DESC
OFFSET 0 ROWS 
FETCH FIRST 10 ROWS only

logs1仍使用正确索引，查询快速；但logs2错误选择了logs2_create_time_idx单字段索引，导致查询耗时超4分钟（扫描全表2亿条数据）。删除logs2_create_time_idx索引后，PostgreSQL会正确使用联合索引，查询恢复快速。

数据量较小时（如200万条）PostgreSQL会选择正确索引，请问该如何解决这个索引选择错误的问题？

解决方案 #

1. 强制指定索引 #

PostgreSQL支持通过INDEX或INDEXES子句强制查询使用指定索引，语法如下：

EXPLAIN ANALYZE
SELECT *
FROM logs2
WHERE user_id IN (1001, 1002)
ORDER BY create_time  DESC
OFFSET 0 ROWS 
FETCH FIRST 10 ROWS only
INDEX logs2_user_id_create_time_idx; -- 强制使用联合索引

如果要指定多个候选索引（让优化器从指定范围内选择），可以用：

SELECT *
FROM logs2
WHERE user_id IN (1001, 1002)
ORDER BY create_time  DESC
OFFSET 0 ROWS 
FETCH FIRST 10 ROWS only
INDEXES (logs2_user_id_create_time_idx, logs2_user_id_idx);

2. 更新统计信息并调整统计参数 #

当数据分布极度倾斜时，默认统计样本可能不足以反映真实分布，可通过以下方式优化：

• 提高表字段的统计样本比例：

ALTER TABLE logs2 ALTER COLUMN user_id SET STATISTICS 10000; -- 最大值为10000
ANALYZE logs2;

• 执行ANALYZE VERBOSE logs2;查看统计信息是否准确反映user_id的分布情况。

3. 删除冗余索引 #

如果logs2_create_time_idx索引在业务中很少被用到，直接删除该冗余索引是最直接的解决方式，避免优化器误选。若该索引有其他业务依赖，则不建议删除。

4. 改写查询语句引导优化器 #

将IN子句拆分为UNION ALL（适合少量IN值的场景），让优化器对每个user_id单独走联合索引，再合并排序：

EXPLAIN ANALYZE
SELECT * FROM logs2 WHERE user_id = 1001
UNION ALL
SELECT * FROM logs2 WHERE user_id = 1002
ORDER BY create_time DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

5. 临时调整优化器成本参数 #

若上述方法无效，可临时调整优化器的成本估算参数，降低索引扫描的相对成本：

-- 临时关闭当前会话的全表扫描
SET enable_seqscan = off;
-- 或者降低随机页读取成本系数，让优化器更倾向于索引扫描
SET random_page_cost = 1.1; -- 默认值为4

注意：该调整为会话级，可能影响其他查询，建议仅在特定会话中临时使用，不要全局修改。

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