PostgreSQL为何不使用多索引？查询性能优化求助

表结构 #

user_id UUID
tenant_id UUID
content_id UUID
created_at timestamp

表中现有近1200万条记录，执行以下查询时耗时接近1分钟：

SELECT * FROM content
    WHERE tenant_id = 'uuid1'
    AND user_id IN ('uuid2')
ORDER BY created_at DESC
LIMIT 100 OFFSET 0;

已创建两个索引：tenant_user_filter_index(tenant_id, user_id) 和 created_at_idx(created_at DESC)。执行EXPLAIN ANALYZE后发现，查询要么使用created_at_idx过滤结果，要么使用tenant_user_filter_index后执行Top N堆排序。

执行计划示例 #

Limit  (cost=561.88..562.13 rows=100 width=24) (actual time=21967.512..21967.529 rows=100 loops=1)
  Buffers: shared hit=258 read=1265
  I/O Timings: read=21946.089
  ->  Sort  (cost=561.88..562.22 rows=136 width=24) (actual time=21967.510..21967.519 rows=100 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 32kB
        Buffers: shared hit=258 read=1265
        I/O Timings: read=21946.089
        ->  Index Scan using tenant_user_filter_index on content  (cost=0.56..557.06 rows=136 width=24) (actual time=69.727..21962.150 rows=27666 loops=1)
"              Index Cond: ((tenant_id = 'uuid1'::uuid) AND (user_id = 'uuid2'::uuid)))"
              Buffers: shared hit=255 read=1265
              I/O Timings: read=21946.089
Planning Time: 0.573 ms
Execution Time: 21967.559 ms

已执行ANALYZE命令更新查询规划器，需排查哪些问题？

• 索引设计缺陷：现有两个索引无法同时满足过滤与排序需求。tenant_user_filter_index仅能快速过滤符合条件的行，但需额外执行排序；created_at_idx可按排序顺序扫描，但需逐一校验tenant_id和user_id匹配性，效率低下。应创建组合索引(tenant_id, user_id, created_at DESC)，该索引可直接过滤目标行，且结果已按created_at降序排列，无需额外排序，直接取前100条即可。
• 统计信息偏差：执行计划中估算符合条件的行数为136，实际却有27666条，偏差极大，说明统计信息可能不准确。虽已执行ANALYZE，可尝试：
  • 针对指定字段增加统计样本量：ANALYZE content (tenant_id, user_id, created_at);
  • 调整default_statistics_target参数后重新执行ANALYZE，确保规划器能准确评估数据分布。
• 磁盘I/O瓶颈：从执行计划看，磁盘读取耗时占比超99%（21946ms），说明大量数据未命中缓存，需从磁盘读取。需排查：
  • 磁盘IOPS、吞吐量是否达标，是否存在队列拥堵情况；
  • shared_buffers配置是否合理，是否有足够内存缓存高频访问数据。
• 数据分布问题：
  • 该tenant_id+user_id组合下数据量达27666条，若此类组合普遍存在大量数据，现有索引无法避免全量扫描；
  • UUID作为索引字段会导致数据分布分散，索引扫描时需频繁随机读取磁盘，影响性能。可考虑替换为紧凑有序的标识符（如bigint自增ID），或针对高频访问的租户和用户创建局部索引。
• 查询优化器参数配置：
  • 检查work_mem设置，确保排序等操作有足够内存支持；
  • 若使用SSD磁盘，需调低random_page_cost参数（建议从默认4调整为1.1），让规划器更倾向于选择索引扫描；
  • 确认enable_sort等核心参数配置合理，避免影响优化器决策。

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