PostgreSQL多列过滤排序慢查询优化：索引创建方案咨询

现有640万条数据的inbox_ticket表，执行以下多列过滤+双列排序的查询时速度极慢，暂未进行任何优化：

select it.conversation_id, it.conversation_source, it.full_name,
    it.last_message_timestamp, it.conversation_read,
    it.id as ticket_id, it.owner_id, it.status, it.priority,
    it.tags, it.last_msg_content, it.last_msg_attachment,
    it.last_msg_actor, it.last_msg_delivered, it.last_msg_read,
    it.last_message_timestamp as message_created_at, it.subject,
    it.closed_at, it.last_message_received_at
from inbox_ticket it
where
    it.merchant_id = 115 and it.status <> 'assigned_to_bot' 
    and it.owner_id in (3486070,60,-1)  
    and it.last_message_timestamp >= '2023-07-01 00:00:00+05:30' 
    and it.last_message_timestamp <= '2023-07-31 00:00:00+05:30'
    and it.last_message_timestamp < '2023-07-31 18:40:00+05:30' 
order by it.last_message_timestamp desc, it.id desc 
limit 25

执行EXPLAIN得到的执行计划：

Limit  (cost=4.18..4.19 rows=1 width=218)
->  Sort  (cost=4.18..4.19 rows=1 width=218)
Sort Key: last_message_timestamp DESC, id DESC
->  Index Scan using inbox_ticket_merchant_id_67656847 on inbox_ticket it  (cost=0.14..4.17 rows=1 width=218)
Index Cond: (merchant_id = 115)
Filter: (((status)::text <> 'assigned_to_bot'::text) AND (last_message_timestamp >= '2023-07-01 00:00:00+05:30'::timestamp with time zone) AND (last_message_timestamp <= '2023-07-31 00:00:00+05:30'::timestamp with time zone) AND (last_message_timestamp < '2023-07-31 18:40:00+05:30'::timestamp with time zone) AND (owner_id = ANY ('{3486070,60,-1}'::integer[])))

疑问 #

• 需为所有过滤列创建单独索引，还是创建包含所有过滤列+排序列（last_message_timestamp desc, id desc）的复合索引？
• 是否需要单独创建排序列的索引？

优化方案 #

优先创建包含等值过滤列+范围过滤列+排序列的复合索引，单独索引或单独排序列索引无法解决当前性能问题，原因如下：

1. 复合索引的最优列顺序
   复合索引列顺序遵循「等值匹配列在前，范围匹配列居中，排序列最后」的原则，结合你的查询场景，推荐创建如下索引：

   CREATE INDEX idx_inbox_ticket_opt ON inbox_ticket
   (merchant_id, owner_id, last_message_timestamp DESC, id DESC);
   

   • merchant_id和owner_id是等值匹配列（=/IN属于等值类匹配），可以根据列的基数调整顺序（基数高的列放前面，过滤效果更好）
   • last_message_timestamp是范围匹配列，同时也是排序的第一列，指定DESC可以让索引直接按查询需要的顺序存储数据
   • id是排序的第二列，追加到索引末尾后，数据库可以直接从索引中获取有序数据，避免额外的Sort操作（从当前EXPLAIN结果看，Sort是性能瓶颈之一）

2. 为什么不推荐单独索引？
   PostgreSQL在单查询中通常只能利用一个最优的单独索引，其余过滤条件需要回表逐行验证，且排序阶段仍需对筛选出的数据做全量排序，无法利用索引的有序性，性能提升有限。

3. 为什么不需要单独排序列索引？
   单独的排序列索引无法结合过滤条件使用，数据库需要先筛选出符合merchant_id/owner_id/时间范围的所有数据，再基于排序索引排序，本质上还是要处理大量数据，无法直接利用索引快速定位到前25条目标数据。

4. 额外优化点
   查询中last_message_timestamp的两个范围条件存在冗余：<= '2023-07-31 00:00:00+05:30'比< '2023-07-31 18:40:00+05:30'更严格，可以简化为：

   and it.last_message_timestamp >= '2023-07-01 00:00:00+05:30' 
   and it.last_message_timestamp <= '2023-07-31 00:00:00+05:30'
   

   简化后不影响索引使用，但能让查询逻辑更清晰。

内容的提问来源于stack exchange，提问作者pramod N K