CODEWITHSUNDEEP
sqlpostgresqlindexing
pomo
pomo

Reputation: 2301

indexing a max + group by query

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

[edit] added a group by col - sorry

I've tried this for example:

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

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

Upvotes: 1

Views: 1011

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

For this query:

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

The best index is (account_id, exchange_id, currency, timestamp desc).

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;

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.

Upvotes: 1

Related Questions