1

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

pomo
  • 2,251
  • 1
  • 21
  • 34
  • "The table isn't large right now ..." -- and maybe that's the reason not using any index seems faster to the optimizer. But I think `(account_id, exchange_id, timestamp DESC)` could be a promising candidate. – sticky bit May 25 '19 at 15:08
  • Thanks, but I tried that (and many other combinations) not no luck - always results in a full scan. ( BTW "not large" means around 27k rows right now... actually don't know if that's considered large or not) – pomo May 25 '19 at 15:13
  • Hmm. Maybe the statistics are stale and an [`ANALYZE`](https://www.postgresql.org/docs/current/sql-analyze.html) brings a change. – sticky bit May 25 '19 at 15:16

1 Answers1

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks but I've tried that (`create index test_idx on exchange_balance (account_id, exchange_id, currency, timestamp desc)`) and my query still results in a full table scan. For the original query at least. It does get used for your suggested alternate query. Are they equivalent (sorry, my sql isn't that great)? – pomo May 26 '19 at 10:15
  • @pomo . . . The two queries are basically equivalent, in that they generate the same result set. If `timestamp` can be `NULL`, you actually want `timestamp desc nulls last` for the `order by`. – Gordon Linoff May 26 '19 at 11:39
  • Thanks, I'll try it out then. That column is NOT NULL – pomo May 26 '19 at 12:19