PostgreSQL使用DISTINCT ON查询时应如何选择合适的索引？

你给出的两个选项（单独创建ts、coin单列索引，或是创建(ts, coin)联合索引）都不是最优解，最优方案是创建(coin, ts DESC)的联合索引，具体原因如下：

PostgreSQL的DISTINCT ON (列名)语法要求对应查询的ORDER BY子句必须把DISTINCT ON指定的列作为第一个排序键，数据库会将排序后相同列值的行归为一组，取每组的第一行作为结果。
而PostgreSQL的B树索引是按索引定义的列顺序有序存储的，只有当索引的前缀排序逻辑和查询的ORDER BY逻辑完全匹配时，才能直接复用索引的有序性，跳过额外的排序步骤，甚至直接走索引只读扫描（Index Only Scan）完成查询。

• 单独创建coin、ts单列索引：无法同时匹配分组+排序的需求。如果走coin索引，拿到同一个币种的所有行后，还需要额外对这些行按ts倒序排序才能取最新记录；如果走ts索引，完全无法支撑按coin分组的逻辑，数据库需要全表扫描后做全局排序，数据量稍大时性能会非常差。
• 创建(ts, coin)联合索引：该索引的存储逻辑是先按ts排序，相同时间戳的记录再按coin排序，和你查询要求的「先按coin排序、同币种再按ts倒序」的逻辑完全不匹配，数据库无法复用该索引的有序性，还是需要做全表排序，对你的这个查询几乎没有优化效果。
• 创建(coin, ts DESC)联合索引：索引的存储顺序和你的查询排序逻辑完全一致，同一个币种的所有记录已经按时间倒序在索引中排好，数据库遍历索引时每遇到一个新的币种直接取第一条记录即可，不需要任何额外排序操作，性能最高。

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