PostgreSQL含多where谓词的查询仅用order by列索引是否生效？

用户给出的查询语句如下：

select * from my_table
where col1 = 'abc' and col2 = 'qwe' and ...  --例如10个甚至更多谓词
order by my_date desc

PostgreSQL是否会使用仅建立在my_date列上的索引？ #

是否选择该索引没有固定结论，完全取决于表的统计信息和查询的实际参数：

• 如果WHERE条件的过滤性极强，过滤后仅剩下少量结果行，PostgreSQL会优先选择匹配WHERE条件的索引（如果存在col1、col2等过滤列的索引），或者直接走全表扫描后排序，不会使用my_date的单列索引。
• 如果WHERE条件过滤性很差，或者没有适配WHERE条件的索引，且查询带有小行数的LIMIT子句，PostgreSQL大概率会选择走my_date的单列索引，按排序好的顺序逐行校验WHERE条件，拿到限定行数后直接终止扫描，避免全量排序的开销。
• 如果查询没有LIMIT子句，无论过滤性如何，PostgreSQL基本都不会选择该索引：因为走索引需要回表查询所有匹配行的完整数据，大量随机IO的开销远高于全表扫描后内存排序的开销。

该索引能否提升查询性能？ #

同样需要分场景判断：

• 可以提升性能的场景：
  • 查询带有小行数的LIMIT，且WHERE条件的过滤率不是极低：这时候走my_date索引可以跳过全量排序，扫到符合条件的限定行数就停止，性能提升非常明显。
  • WHERE条件过滤性极差，几乎要返回全表所有行：这时候走my_date索引顺序扫描回表，可以避免全表扫描后对大量数据做排序（尤其是排序需要落盘的场景），性能提升显著。
• 无法提升甚至降低性能的场景：
  • WHERE条件过滤性极强，过滤后仅剩下少量行：这时候无论走过滤列索引还是全表扫描后排序，开销都远低于走my_date索引产生的随机IO开销，用该索引反而更慢。
  • 查询没有LIMIT且需要返回的行数较多：这时候走索引产生的大量随机IO开销远高于排序开销，用索引会导致性能下降。

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