How do I query out the largest value using an index

Question

I have a table with several terabytes of event data in a very simple (id, bucket_id, data, created_at) schema and there is an index like so

create index index_events_on_created_at_and_bucket_id
    on public.events (created_at desc, bucket_id asc);

Now I thought it would be fast to find the id of the most recent event in each bucket with a query like:

select max(created_at), bucket_id from events group by bucket_id;

Explain output:

HashAggregate  (cost=170172168.62..170172178.41 rows=979 width=16)
  Group Key: bucket_id
  ->  Index Only Scan using index_events_on_created_at_and_bucket_id on events  (cost=0.70..156003994.34 rows=2833634856 width=16)

It seems to be using the index, but doing an index scan instead of just grabbing the head value like I expected. Either way, it does not complete in a timely manner. I suppose it's a problem with using the aggregate function in the query, but I don't know how to fix it.

Is there a query that can return the most recent (i.e. first in the index) created_at timestamp for each bucket by fetching it out of this index?

Answer 1

Better index with leading bucket_id

You want one row per bucket. An index with leading bucket_id will be much more useful.

CREATE INDEX events_bucket_id_created_at_idx ON events (bucket_id, created_at DESC);

Related:

• Is a composite index also good for queries on the first field?

Since you have a very small number of distinct values in bucket_id ("rows=979"), this query technique should give you dramatically faster results, based on my suggested index:

WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT bucket_id, created_at
   FROM   events
   ORDER  BY bucket_id, created_at DESC
   LIMIT 1
   )
   
   UNION ALL
   SELECT e.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT e.bucket_id, e.created_at
      FROM   events e
      WHERE  e.bucket_id > c.bucket_id
      ORDER  BY e.bucket_id, e.created_at DESC
      LIMIT  1
      ) e
   WHERE  c.bucket_id IS NOT NULL
   )
SELECT * FROM cte
WHERE  bucket_id IS NOT NULL;

It emulates a "loose index scan", only picking the "first" row for every distinct bucket_id - exactly what you are looking for.

Note how the sort order in the query meticulously matches the index.

If the visibility map of the table is up to date (i.e. the table is vacuum'ed enough), you get index-only scans. Should apply, since the slow query you demonstrated got an index-only scan, too. (Though that's scanning the whole index instead of just leading entries per bucket). Related:

• Can PostgreSQL use indexes to expedite count(distinct) queries?

This assumes both columns of interest to be NOT NULL. Else you have to do more.

If you also have a table bucket with one row per relevant bucket_id, this is even a bit faster, yet:

SELECT b.bucket_id, e.created_at
FROM   bucket b
CROSS  JOIN LATERAL (
   SELECT e.created_at
   FROM   events e
   WHERE  e.bucket_id = b.bucket_id
   ORDER  BY e.created_at DESC
   LIMIT  1
   ) e
ORDER  BY b.bucket_id;

See:

• SELECT DISTINCT is slower than expected on my table in PostgreSQL
• Optimize GROUP BY query to retrieve latest row per user

Stuck with index on (created_at DESC, bucket_id ASC)

We can work with the additional meta information from your comments:

I know all the buckets I care about have recent events

You can enhance the queries above, but a different angle based on that should perform better:

SELECT DISTINCT ON (bucket_id)
       bucket_id, created_at
FROM   events
WHERE  created_at > now() - interval '15 minutes'  -- adapt as needed
ORDER  BY bucket_id, created_at DESC;

Should be faster when limited to the tiny (?) fraction of the most recent rows. Postgres can read top rows from the index and feed that to DISTINCT ON. About DISTINCT ON:

• Select first row in each GROUP BY group?