Huge performance difference for the same SQL query with different parameter

Question

I'm trying to understand why my queries are having huge performance difference in my case.

I have this table has columns: timestamp, ticker, open, high, low, close, volume, exchange. The database is Postgres 9.6.

The table is around 300 million rows.

I've built the following index:

unique index on (timestamp, ticker, exchange),
index on (ticker),
index on (exchange).

My query is as follows

SELECT MAX(timestamp) FROM table WHERE ticker='ticker1' AND exchange='exchange1';

But for different values for tickers, I'm having huge query time difference, ranging from 300ms to 7mins.

I'm trying to understand what is causing this and if I can improve it in any ways.

More information:

create table ohlcv (
    timestamp bigint,
    ticker varchar(20),
    open double precision,
    high double precision,
    low double precision,
    close double precision,
    volume double precision,
    exchange varchar(20),
    constraint ohlcv_timestamp_ticker_exchange_key
        unique (timestamp, ticker, exchange)
);

create index ohlcv_exchange_index on ohlcv (exchange);
create index ohlcv_ticker_index on ohlcv (ticker);

Please edit your question and add both execution plans (the slow and the fast) generated using explain (analyze, buffers, format text) (not just a "simple" explain) as formatted text and make sure you prevent the indention of the plan. Paste the text, then put ``` on the line before the plan and on a line after the plan. Please also include complete create index statements for all indexes as well. — user330315
– user330315, Commented Sep 3, 2019 at 20:59

Laurenz Albe · Accepted Answer · 2019-09-04 05:53:45Z

2

You need this index:

CREATE INDEX ON "table" (ticker, exchange, timestamp);

It can find the rows satisfying the WHERE condition quickly, and then the max can be found very quickly.

Since you already have a unique constraint with these columns (but in the wrong order), you can drop and re-create the constraint. That will have the same effect, since it is backed by an index.

edited Sep 4, 2019 at 5:53

answered Sep 3, 2019 at 22:10

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

qichao_he Over a year ago

For postgres, when creating constraint, an unique index is created, so I already have an unique index on (timestamp, ticker, exchange). In this case will your solution help? Also postgres has the ability to use multiple indexes at the same time, I believe: postgresql.org/docs/9.4/indexes-unique.html, postgresql.org/docs/9.3/indexes-bitmap-scans.html

Collectives™ on Stack Overflow

Huge performance difference for the same SQL query with different parameter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related