通过查询索引最大+组 [英] indexing a max + group by query

问题描述

如何为此查询创建索引?我已经尝试过可以想到的所有可能的索引组合，但解释计划总是显示正在使用SEQ_SCAN.

How do create an index for this query? I've tried every possible combination of indexes I can think of but explain plan always shows a SEQ_SCAN is being used.

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

该表现在不大，因此实际上速度非常快，但它会快速增长.

The table isn't large right now and so it's actually quite fast, but it will grow quickly.

PostgreSQL 9.6

PostgreSQL 9.6

[edit]按col添加了一个群组-抱歉

[edit] added a group by col - sorry

我已经尝试过例如:

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

但总是对具有4.5万行的表进行表扫描

But always a table scan on a table with 45k rows

推荐答案

对于此查询:

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

最佳索引是(帐户ID，Exchange_ID，货币，时间戳记).

在Postgres中，使用以下命令可能会更有效:

In Postgres, this might be more efficient using:

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 .但是保留它们可以使查询推广到多个帐户.

Strictly speaking, account_id is not needed in the order by or distinct on clause. But keeping them allows the query to generalize to multiple accounts.

这篇关于通过查询索引最大+组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！