sql - 通过查询索引 max + group

Question

如何为这个查询创建索引？我已经尝试了我能想到的所有可能的索引组合，但解释计划总是显示正在使用 SEQ_SCAN。

select exchange_id, currency, max(timestamp) timestamp2
    from exchange_balance
    where account_id = 'foo'
    group by exchange_id, currency

该表现在并不大，所以实际上速度很快，但它会增长得很快。

PostgreSQL 9.6

[edit] 通过 col 添加了一个组 - 抱歉

我试过这个例子:

CREATE INDEX idx_exchange_balance_1 ON exchange_balance (exchange_id, currency, timestamp desc, account_id);

但总是对具有 45k 行的表进行表扫描

Answer 1

对于这个查询:

select exchange_id, currency, max(timestamp) as timestamp2
from exchange_balance
where account_id = 'foo'
group by exchange_id, currency;

最好的索引是(account_id, exchange_id, currency, timestamp desc)。

在 Postgres 中，使用以下方法可能更有效:

select distinct on (account_id, exchange_id, currency) exchange_id, currency, timestamp
from exchange_balance
where account_id = 'foo'
order by account_id, exchange_id, currency, timestamp desc;

严格来说，order by 或distinct on 子句中不需要account_id。但保留它们允许查询推广到多个帐户。