CODEWITHSUNDEEP
postgresqlindexing
qichao_he
qichao_he

Reputation: 5454

Huge performance difference for the same SQL query with different parameter

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:

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);

Upvotes: 0

Views: 507

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247270

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.

Upvotes: 2

Related Questions