Postgres index for finding the most recent row

Viewed 347

I've got a table with 1.5 million rows. It will get significantly bigger than this. I have a very simple query that I think I should be able to index to be lightning fast.

select * from instant_power_reads where site_id = 22 order by read_at desc limit 1;

I've been reading around and see most people saying that a combined index on site_id and read_at should take care of this, but I can't get it to work.

My index looks like this:

CREATE INDEX idx_ipr_site_read
ON public.instant_power_reads USING btree
(site_id ASC NULLS LAST, read_at DESC NULLS LAST)
TABLESPACE pg_default;

When I run the query, it takes about a third of a second, which I believe is too long. This is what the explain tells me. It doesn't seem to be using the index like I think it should. Shouldn't this be able to do a quick "Index Only Scan"?

Limit  (cost=10904.20..10904.21 rows=1 width=30)
  ->  Sort  (cost=10904.20..10959.73 rows=22209 width=30)
        Sort Key: read_at DESC
        ->  Bitmap Heap Scan on instant_power_reads  (cost=516.55..10793.16 rows=22209 width=30)
              Recheck Cond: (site_id = 22)
              ->  Bitmap Index Scan on idx_ipr_site_read  (cost=0.00..511.00 rows=22209 width=0)
                    Index Cond: (site_id = 22)

I know I have other options. My main idea is to maintain a separate temp table that just contains the latest records for each site. But I'd like to learn what I'm doing wrong here.

EDIT: I was asked to post the EXPLAIN (ANALYZE, BUFFERS) for the basic index.

Limit  (cost=0.43..2.24 rows=1 width=30) (actual time=0.027..0.028 rows=1 loops=1)
  Buffers: shared hit=4
  ->  Index Scan Backward using idx_ipr_site_read on instant_power_reads  (cost=0.43..40405.75 rows=22288 width=30) (actual time=0.026..0.026 rows=1 loops=1)
    Index Cond: (site_id = 22)
    Buffers: shared hit=4
Planning Time: 0.124 ms
Execution Time: 0.042 ms

EDIT 2: I accepted Laurenz' answer, but wanted to specify that the key to figuring this out was running "EXPLAIN (ANALYZE, BUFFERS)" because it shows the actual planning and execution time, which may be much different from the time your GUI (like pgAdmin) is showing you.

1 Answers

Looks like the obvious index is the right choice:

CREATE INDEX ON instant_power_reads (site_id, read_at);

If you measure a long execution time on the client side, it is probably network latency.

Related