5

I have a time series table prices in a PostgreSQL 10 DB.
Here is a simplified test case to illustrate the problem:

CREATE TABLE prices (
    currency text NOT NULL,
    side     boolean NOT NULL,
    price    numeric NOT NULL,
    ts       timestamptz NOT NULL
);

I want to quickly query the last values of each currency/side duo, as this would give me the current buy/sell price of each currency.

My current solution is:

create index on prices (currency, side, ts desc);

select distinct on (currency, side) *
 order by currency, side, ts desc;

But this will give me very slow queries (~500ms) in this table with only ~30k rows.

The actual table has four columns that I want to group, instead of two. Here is what the actual table and query really looks like:

create table prices (
    exchange integer not null,
    pair text not null,
    side boolean not null,
    guaranteed_volume numeric not null,
    ts timestamp with time zone not null,
    price numeric not null,
    constraint prices_pkey primary key (exchange, pair, side, guaranteed_volume, ts),
    constraint prices_exchange_fkey foreign key (exchange)
        references exchanges (id) match simple
        on update no action
        on delete no action
);

create index prices_exchange_pair_side_guaranteed_volume_ts_idx
      on prices (exchange, pair, side, guaranteed_volume, ts desc);

create view last_prices as
select distinct on (exchange, pair, side, guaranteed_volume)
       exchange
     , pair
     , side
     , guaranteed_volume
     , price
     , ts
  from prices
 order by exchange
        , pair
        , side
        , guaranteed_volume
        , ts desc;

There are 34441 rows, currently. Some useful debug queries:

# explain (analyze,buffers) select * from last_prices;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=2662.03..2997.71 rows=1224 width=37) (actual time=403.218..459.041 rows=392 loops=1)
   Buffers: shared hit=418
   ->  Sort  (cost=2662.03..2729.17 rows=26854 width=37) (actual time=403.213..411.041 rows=28353 loops=1)
         Sort Key: prices.exchange, prices.pair, prices.side, prices.guaranteed_volume, prices.ts DESC
         Sort Method: quicksort  Memory: 2984kB
         Buffers: shared hit=418
         ->  Seq Scan on prices  (cost=0.00..686.54 rows=26854 width=37) (actual time=0.022..31.407 rows=28353 loops=1)
               Buffers: shared hit=418
 Planning time: 0.911 ms
 Execution time: 460.190 ms

Explain analyze with seqscan disabled:

# explain (analyze,buffers) select * from last_prices;
                                                                                  QUERY PLAN                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=0.41..4458.07 rows=1224 width=37) (actual time=0.037..122.237 rows=392 loops=1)
   Buffers: shared hit=15182
   ->  Index Scan using prices_exchange_pair_side_guaranteed_volume_ts_idx on prices  (cost=0.41..4189.53 rows=26854 width=37) (actual time=0.034..91.237 rows=29649 loops=1)
         Buffers: shared hit=15182
 Planning time: 0.291 ms
 Execution time: 122.417 ms

Adding a query with the view's query being accessed directly:

# explain (analyze, buffers)
select distinct on (exchange, pair, side, guaranteed_volume)
       exchange
     , pair
     , side
     , guaranteed_volume
     , price
     , ts
  from prices
 order by exchange
        , pair
        , side
        , guaranteed_volume
        , ts desc;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=2163.56..2429.99 rows=1224 width=37) (actual time=364.716..391.405 rows=380 loops=1)
   Buffers: shared hit=418
   ->  Sort  (cost=2163.56..2216.85 rows=21314 width=37) (actual time=364.711..370.458 rows=24011 loops=1)
         Sort Key: exchange, pair, side, guaranteed_volume, ts DESC
         Sort Method: quicksort  Memory: 2644kB
         Buffers: shared hit=418
         ->  Seq Scan on prices  (cost=0.00..631.14 rows=21314 width=37) (actual time=0.025..13.751 rows=24011 loops=1)
               Buffers: shared hit=418
 Planning time: 0.258 ms
 Execution time: 392.110 ms
  • 1
    What happens if you run the statement from the view directly? – a_horse_with_no_name Mar 26 '18 at 13:41
  • you could also use topn if you want to maintain an aggregate table. – Evan Carroll Mar 28 '18 at 20:07
4
+50

I want to quickly query the last values of each currency/side duo

DISTINCT ON excels for few rows per combination of interest. But your use case obviously has many rows per distinct (currency, side). So DISTINCT ON is a bad choice as far as performance is concerned. You'll find a detailed assessment and an arsenal of solutions in these two related answer on SO:

If all you need is the latest timestamp ts, the column is sort criteria and desired return value in one and the case is very simple. Look to Evan's simple solution with max(ts).

(Well, ideally, you'd have an index on (currency, side, ts desc NULLS LAST), since max(ts) ignores NULL values and better matches this sort order. But that won't matter much with a column defined NOT NULL.)

Typically, you need additional columns from each selected row (like the current price!) and/or you need to sort by multiple columns, so you need to do more.

Ideally, you have another table listing all currencies - and a FK constraint to enforce referential integrity and disallow nonexistent currency values. Then use the query technique from chapter "2a. LATERAL join" in the linked answer, expanded to account for the added side:

Based on your initial simple test case:

SELECT c.currency, s.side, p.*
FROM   currency c
CROSS  JOIN (VALUES (true), (false)) s(side)  -- account for side
CROSS  JOIN LATERAL (
   SELECT ts, price              -- more columns?
   FROM   prices
   WHERE  currency = c.currency
   AND    side = s.side
   ORDER  BY ts DESC             -- ts is NOT NULL
   LIMIT  1
   ) p
ORDER  BY 1, 2;  -- optional, whatever you prefer;

You should see very fast index scans on an index on (currency, side, ts DESC).

If index-only scans are possible and you only need ts and price it might pay to add price as last column to the index.

dbfiddle here

Whether you save this query in a VIEW or not doesn't affect performance.

  • Will try this today! On a side note, your answers have saved me a lot of times. You are my stackexchange hero haha thanks man – ivarec Mar 28 '18 at 16:47
  • 1
    This works very well! Index scans are now taking less than 100ms for very big tables. Thanks! Could you just help me understand better the solution? In the cross join with the "side" column, you knew all possible values for it (since it is a boolean) and used that in the query. What if I want to include a new column that represents an "exchange", which would be an integer column of ids? Should I use distinct on first to get all exchanges and then do what you did with "side"? – ivarec Mar 28 '18 at 18:54
  • Just tested like I suggested above and it worked fine. Let me know if using "select distinct on" in the cross join queries is a bad idea. And again, thank you very much! – ivarec Mar 28 '18 at 19:00
  • 1
    @ivarec: Nothing wrong with DISTINCT ON in a subquery (if that is fast). Or you CROSS JOIN to another table with distinct values for exchange to produce all combinations of interest. Or, if there are too many irrelevant combinations, switch to the rCTE technique laid out in chapter "1. No separate table with unique users" of the linked answer. Read the two answers linked above for detailed explanation. – Erwin Brandstetter Mar 28 '18 at 21:38
1

If you have the index on (currency, side, ts desc), what's the time like for:

SELECT currency, side, max(ts)
FROM prices
GROUP BY currency, side;

It gets much faster, but then again, the reason that I used distinct on was to get the price value associated with the last ts. – ivarec 56 mins ago

  • It gets much faster, but then again, the reason that I used distinct on was to get the price value associated with the last ts. – ivarec Mar 28 '18 at 18:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.