PostgreSQL高效结合SELECT DISTINCT、ORDER BY与小LIMIT的方法

表结构与现有索引 #

表定义：

create table transactions
(
    transaction_id      integer  not null,
    transaction_timestamp integer not null,
    input_index         smallint,
    output_index        smallint not null,
    from_id             integer,
    to_id               integer  not null,
    input_value         real,
    output_value        real     not null,
    constraint unique_transactions
        unique (transaction_id, from_id, to_id)
);

已创建索引：

create index idx_transactions_from_id_block_timestamp
    on transactions (from_id asc, transaction_timestamp desc);

create index idx_transactions_to_id_block_timestamp
    on transactions (to_id asc, transaction_timestamp desc);

create index idx_transactions_transaction_id
    on transactions (transaction_id);

create index idx_transactions_block_timestamp
    on transactions (transaction_timestamp desc);

需求与原问题 #

需要获取to_id = 1000且transaction_timestamp在1691193600~1711929600范围内的最新10个唯一(transaction_id, output_index)对，每个唯一对任意保留一条记录即可，核心是优先取时间戳最新的记录。

原尝试的DISTINCT ON写法因PostgreSQL语法限制报错（要求DISTINCT ON的列必须是ORDER BY的起始列），调整顺序后无法满足按时间倒序取最新记录的需求；之前用窗口函数或子查询的方式，都因为需要扫描全量符合条件的数据而性能极低。

高效解决方案 #

优化查询语句

利用现有idx_transactions_to_id_block_timestamp索引（to_id+transaction_timestamp desc），让数据库按时间倒序读取数据，一旦收集到10个唯一的(transaction_id, output_index)对就终止扫描，避免全表遍历：

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY transaction_id, output_index 
               ORDER BY transaction_timestamp DESC
           ) AS rn
    FROM transactions
    WHERE to_id = 1000
      AND transaction_timestamp BETWEEN 1691193600 AND 1711929600
    -- 强制利用索引按时间倒序读取，尽早终止扫描
    ORDER BY transaction_timestamp DESC
) AS t
WHERE rn = 1
LIMIT 10;

进一步优化：创建覆盖索引

如果想要极致性能，可以创建一个覆盖查询所有需求字段的索引，避免回表操作：

CREATE INDEX idx_transactions_to_id_ts_txid_outidx
ON transactions (to_id ASC, transaction_timestamp DESC, transaction_id, output_index);

这个索引包含了过滤条件、排序字段和去重所需的字段，数据库可以直接从索引中获取数据，无需访问主表。

为什么之前的查询慢 #

• 查询1：窗口函数需要先扫描所有符合条件的数据，完成分区排名后再排序取10条，无法利用LIMIT提前终止扫描，必须遍历全量符合条件的记录；
• 查询2：子查询先按transaction_id, output_index排序去重，再外层按时间戳排序，同样需要扫描大量数据，无法借助时间倒序的优势提前终止。

而优化后的查询，数据库会通过索引快速读取最新的记录，每遇到一个新的(transaction_id, output_index)对就标记为有效（rn=1），一旦收集够10条有效记录就停止扫描，性能会大幅提升。

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