Optimizing a 'latest' query in Postgres on 20M rows

Question

My table looks as follows:

    Column             |    Type           |    
-----------------------+-------------------+
 id                    | integer           | 
 source_id             | integer           | 
 timestamp             | integer           | 
 observation_timestamp | integer           | 
 value                 | double precision  | 

indexes exist on source_id, timestamp and on a combo of timestamp and id (CREATE INDEX timeseries_id_timestamp_combo_idx ON timeseries (id, timeseries DESC NULLS LAST))

There's 20M rows in it (OK, there's 120M, but 20M with source_id = 1). It has many entries for the same timestamp with varying observation_timestamp, which describe a value occurred at timestamp reported or observed at observation_timestamp. e.g. The temperature predicted for tomorrow 2pm as predicted today at 12am.

Ideally this table does a few things well:

• batch inserting new entries, sometimes 100K at a time
• selecting data observed for timeranges ("what's the temperature predictions for January until March")
• selecting data observed for timeranges as observed from a certain point ("what's the view of temperature predictions for January until March as we thought of on November 1st")

The second one is the one that is central to this question.

Data in the table would look like the following

id  source_id   timestamp   observation_timestamp   value
1   1           1531084900  1531083900              9999
2   1           1531084900  1531082900              1111
3   1           1531085900  1531083900              8888
4   1           1531085900  1531082900              7777
5   1           1531086900  1531082900              5555

and an output of the query would look like the following (only the row of the latest observation_timestamp represented)

id  source_id   timestamp   observation_timestamp   value
1   1           1531084900  1531083900              9999
3   1           1531085900  1531083900              8888
5   1           1531086900  1531082900              5555

I've consulted some material prior already to optimize these queries, namely

• https://stackoverflow.com/questions/25536422/optimize-group-by-query-to-retrieve-latest-record-per-user/25536748#25536748
• How to make DISTINCT ON faster in PostgreSQL?
• https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group

... with limited success.

I've considered creating a separate table with timestamp in it so it's easier to laterally reference, but due to the relatively high cardinality of those I doubt whether they'll help me -- additionally I'm concerned that it will hinder to accomplish batch inserting new entries.

---

I'm looking at three queries, and they all give me bad performance

• Recursive CTE with LATERAL join
• Window function
• DISTINCT ON

(I'm aware they don't quite do the same thing at the moment, but they serve as good illustrations of the type of querying as far as I see.)

Recursive CTE with LATERAL join

WITH RECURSIVE cte AS (
    (
        SELECT ts
        FROM timeseries ts
        WHERE source_id = 1
        ORDER BY id, "timestamp" DESC NULLS LAST
        LIMIT 1
    )
    UNION ALL
    SELECT (
        SELECT ts1
        FROM timeseries ts1
        WHERE id > (c.ts).id
        AND source_id = 1
        ORDER BY id, "timestamp" DESC NULLS LAST
        LIMIT 1
    )
    FROM cte c
    WHERE (c.ts).id IS NOT NULL
)
SELECT (ts).*
FROM cte
WHERE (ts).id IS NOT NULL
ORDER BY (ts).id;

Performance:

Sort  (cost=164999681.98..164999682.23 rows=100 width=28)
  Sort Key: ((cte.ts).id)
  CTE cte
    ->  Recursive Union  (cost=1653078.24..164999676.64 rows=101 width=52)
          ->  Subquery Scan on *SELECT* 1  (cost=1653078.24..1653078.26 rows=1 width=52)
                ->  Limit  (cost=1653078.24..1653078.25 rows=1 width=60)
                      ->  Sort  (cost=1653078.24..1702109.00 rows=19612304 width=60)
                            Sort Key: ts.id, ts.timestamp DESC NULLS LAST
                            ->  Bitmap Heap Scan on timeseries ts  (cost=372587.92..1555016.72 rows=19612304 width=60)
                                  Recheck Cond: (source_id = 1)
                                  ->  Bitmap Index Scan on ix_timeseries_source_id  (cost=0.00..367684.85 rows=19612304 width=0)
                                        Index Cond: (source_id = 1)
          ->  WorkTable Scan on cte c  (cost=0.00..16334659.64 rows=10 width=32)
                Filter: ((ts).id IS NOT NULL)
                SubPlan 1
                  ->  Limit  (cost=1633465.94..1633465.94 rows=1 width=60)
                        ->  Sort  (cost=1633465.94..1649809.53 rows=6537435 width=60)
                              Sort Key: ts1.id, ts1.timestamp DESC NULLS LAST
                              ->  Bitmap Heap Scan on timeseries ts1  (cost=369319.21..1600778.77 rows=6537435 width=60)
                                    Recheck Cond: (source_id = 1)
                                    Filter: (id > (c.ts).id)
                                    ->  Bitmap Index Scan on ix_timeseries_source_id  (cost=0.00..367684.85 rows=19612304 width=0)
                                          Index Cond: (source_id = 1)
  ->  CTE Scan on cte  (cost=0.00..2.02 rows=100 width=28)
        Filter: ((ts).id IS NOT NULL)

(only EXPLAIN, EXPLAIN ANALYZE couldn't complete, took >24hrs to complete query)

Window function

WITH summary AS (
  SELECT ts.id, ts.source_id, ts.value,
    ROW_NUMBER() OVER(PARTITION BY ts.timestamp ORDER BY ts.observation_timestamp DESC) AS rn
  FROM timeseries ts
  WHERE source_id = 1
)
SELECT s.*
FROM summary s
WHERE s.rn = 1;

Performance:

CTE Scan on summary s  (cost=5530627.97..5971995.66 rows=98082 width=24) (actual time=150368.441..226331.286 rows=88404 loops=1)
  Filter: (rn = 1)
  Rows Removed by Filter: 20673704
  CTE summary
    ->  WindowAgg  (cost=5138301.13..5530627.97 rows=19616342 width=32) (actual time=150368.429..171189.504 rows=20762108 loops=1)
          ->  Sort  (cost=5138301.13..5187341.98 rows=19616342 width=24) (actual time=150368.405..165390.033 rows=20762108 loops=1)
                Sort Key: ts.timestamp, ts.observation_timestamp DESC
                Sort Method: external merge  Disk: 689752kB
                ->  Bitmap Heap Scan on timeseries ts  (cost=372675.22..1555347.49 rows=19616342 width=24) (actual time=2767.542..50399.741 rows=20762108 loops=1)
                      Recheck Cond: (source_id = 1)
                      Rows Removed by Index Recheck: 217784
                      Heap Blocks: exact=48415 lossy=106652
                      ->  Bitmap Index Scan on ix_timeseries_source_id  (cost=0.00..367771.13 rows=19616342 width=0) (actual time=2757.245..2757.245 rows=20762630 loops=1)
                            Index Cond: (source_id = 1)
Planning time: 0.186 ms
Execution time: 234883.090 ms

DISTINCT ON

SELECT DISTINCT ON (timestamp) *
FROM timeseries
WHERE source_id = 1
ORDER BY timestamp, observation_timestamp DESC;

Performance:

Unique  (cost=5339449.63..5437531.34 rows=15991 width=28) (actual time=112653.438..121397.944 rows=88404 loops=1)
  ->  Sort  (cost=5339449.63..5388490.48 rows=19616342 width=28) (actual time=112653.437..120175.512 rows=20762108 loops=1)
        Sort Key: timestamp, observation_timestamp DESC
        Sort Method: external merge  Disk: 770888kB
        ->  Bitmap Heap Scan on timeseries  (cost=372675.22..1555347.49 rows=19616342 width=28) (actual time=2091.585..56109.942 rows=20762108 loops=1)
              Recheck Cond: (source_id = 1)
              Rows Removed by Index Recheck: 217784
              Heap Blocks: exact=48415 lossy=106652
              ->  Bitmap Index Scan on ix_timeseries_source_id  (cost=0.00..367771.13 rows=19616342 width=0) (actual time=2080.054..2080.054 rows=20762630 loops=1)
                    Index Cond: (source_id = 1)
Planning time: 0.132 ms
Execution time: 161651.006 ms

---

How should I structure my data, are there scans that shouldn't be there, is it generally possible to get these queries to ~1s (instead of ~120s)?

Is there a different way of querying the data to get the results I wanted?

If not, what different infrastructure / architecture should I be looking at?

Answer 1

With your recursive CTE query, the final ORDER BY (ts).id is unnecessary as the CTE automatically creates them in that order. Removing that should make the query much faster, it can stop early rather than generating 20,180,572 rows only to throw all but 500 away. Also, building the index on (source_id, id, timestamp desc nulls last) should improve it further.

For the other two queries, increasing work_mem enough that the bitmaps fit in memory (to get rid of lossy heap blocks) would help some. But not as much as custom indexes, such as (source_id, "timestamp", observation_timestamp DESC) or better yet for index only scans (source_id, "timestamp", observation_timestamp DESC, value, id).