In this post I'll show you several approaches you can use if you need to solve tasks along the lines of "retrieve the earliest/oldest/last etc row per group" from your database. I rank them by how performant I found them to be on a made-up dataset I made for this purpose.
I explain what determines the different timings with the help of visualisations showing how the database processes the query internally. As you will see, we will go from hundreds of ms to about 5, which is quite a significant difference! It is not guaranteed that you will have the same results on your production environment as the data distribution might be different. However if it's reasonably close to my dataset below, you'll be pleasantly surprised.
To support the story in the next sections we'll use a scenario involving meters that regularly record readings. We'll retrieve the latest reading per every meter.
Setup
Let's create a small schema for demonstration purposes. I'm using Postgres version 16.2.
First, we create the table of meters.
create table meters
(
id bigserial,
... other columns
primary key (id)
);
Then, we create the table of readings.
create table readings
(
id bigserial,
meter_id bigint,
date date, <--- or timestampz, etc, but I keep it simple
reading double precision,
primary key (id),
constraint fk__readings_meters foreign key (meter_id) references meters (id)
);
At this point we have our tables in place. For integrity, they are linked them together with the help of a foreign key constraint. Let's now populate our tables with some rows. Let's add 500 meters, with one reading every day, for a year:
insert into meters select * from generate_series(1, 500) seq;
insert into readings(meter_id, date, reading)
select m.id, seq, random() from generate_series('2024-02-01'::date, '2025-02-01'::date, '1 day'::interval) seq, meters m;
Our schema is ready to be queried. Let's start somewhere. This approach might not be the first that comes to your mind, I start with it because I found it to be slowest. This is by no means a reflection of the feature utility as a whole, also not on its performance for other scenarios.
1. Window functions: ~250ms
explain(analyse, buffers)
with readings_with_rownums as (
select
meters.id as meter_id,
readings.reading as reading,
row_number() over (partition by meters.id order by readings.date desc) as rownum
from
readings
join
meters on meters.id = readings.meter_id
)
select
meter_id,
reading
from
readings_with_rownums
where
readings_with_rownums.rownum = 1;
This runs in about 250ms. Let's have a look at the explain plan to understand it better. To visualise it, I am using the excellent visualisation tool from Dalibo.
I've opened the relevant nodes. In the upper part of the picture above, we can notice that it's relatively slow because the query efficiency is low. It discards ~99.73% of rows that it read. In the Sort node arrive 183k rows from the nodes below, but then, only 500 are returned. While not fast, we can also conclude that this approach is not scalable, because it is very sensitive to increases in the dataset.
2. DISTINCT ON: ~250ms
Let's try another approach, this time using the DISTINCT ON clause. For details about how it works, you can read about it in the Postgres docs for SELECT, section DISTINCT clause. Here's how it looks like when it is used to retrieve our latest reading per meter:
explain (analyse, buffers)
select
distinct on (meters.id)
meters.id as meter_id,
readings.reading as reading
from
meters
join
readings on meters.id = readings.meter_id
order by
meters.id,
readings.date desc;
I find this approach very elegant, but I didn't get a noticeable difference with regards to how fast it runs compared with the previous approach. The explain plan looks quite similar to the one we have seen before.
We can observe that it doesn't have the WindowAgg node, but this didn't get us far. It's is still inefficient, reading a lot of rows and discarding the majority before returning the results. But one thing I noticed is the following line in the explain plan:
Sort Method: external merge Disk: 3968kB
This is slowing the sort down because it uses the external disk. This happens when the work_mem setting is too low given the size of the dataset, and the sort can't be done fully in memory, so it spills to disk. Indeed, the default setting for work_mem in Postgres is 4MB.
Let's increase it to make sure it's sufficient.
set work_mem='16MB';
I don't have to restart Postgres for this to take effect. For others we have to.
We retry the query now, we confirm that the sort happens in memory now; the proof is that we can see the following detail in the explain plan:
Sort Method: quicksort Memory: 14951kB
Cool! Did this make a difference though? Not really, we're still at ~250ms. Remember that this is on my laptop, and there is no network between the disk and the CPU. For example in Amazon RDS, the storage part is handled by EBS, which is network-attached so the difference would be quite noticeable in that scenario. The data has to travel more during the sort procedure then.
3. MAX(DATE): ~115ms
No problem, we still have options. Let's try something else. Let's look at another alternative. How about this?
explain(analyse, buffers)
with latest_reads_per_meter as (
select
readings.meter_id as meter_id,
max(date) as reading_date
from
readings
group by
readings.meter_id
)
select
readings.meter_id,
readings.reading
from
meters
join
readings on meters.id = readings.meter_id
join
latest_reads_per_meter lrpm on lrpm.meter_id = meters.id
and readings.date = lrpm.reading_date;
As usual, the explain plan:
Hmm, this looks a bit different than what we have seen before. In a good way! It's now doing the sort much earlier so it's discarding the irrelevant rows earlier. It doesn't carry it over all the way to the end of the retrieval process. This will consume less memory because the intermediate results are smaller. However, does it speed up our query?
Indeed it does!
Execution Time: 114.942 ms
Finally some solid progress. We cut the runtime in half. This is already quite good. But can we do better? You bet, even reduce it by one order of magnitude.
4. Loose index scans: ~14ms
Let's have a look at how the loose index scan works.
First, let's create the required index. The columns have to be defined exactly in this order, with the "grouping" element first and then the other column which will be used for determining "latest" within a group.
create index idx on readings(meter_id, date);
explain (analyse, buffers)
select
meters.id as meter_id,
readings.reading as reading
from
meters
cross join lateral (
select
*
from
readings
where
readings.meter_id = meters.id
order by
date desc
limit 1
) readings;
Execution Time: 13.814 ms
Whoa! What's the reason this is much more performant? For starters - you don't see the 183k rows anywhere at all, not even in the lower nodes that happen in the beginning. We are not sorting anything anymore, because the index keeps our data sorted, at the expense of insertion overhead.
Let's push the envelope even more. Let's open the IO & Buffers tab of the index scan node in the above explain plan and have a look in there. Here it is:
What happens is that first the index is traversed to determine which rows that have to be retrieved to satisfy the query, however after this step, Postgres has to actually go ahead and retrieve the rows from a different place, namely the table (or heap). You might wonder, can we avoid this extra step to read from the table? You bet!
5. Loose index-only scans: ~5ms
We can implement an index-only scan. We use the include option when creating the index to achieve this, like so:
create index idx_with_including on readings(meter_id, date) include (reading);
Let's retry the query and look at the relevant tabs again.
Two important things to observe here. First, notice the Heap Fetches: 0, which indicates that it does not go to the heap to get the rows because they are in the index already. Secondly, the total number of blocks is now ~1500, which means it's 500 less than before. This confirms again that it doesn't go to the heap.
Let's look at the final result. How fast did we get it?
Execution Time: 5.448 ms
This is very nice. But let's understand it a bit better what's happening under the hood.
The following is a visualisation of what Postgres is doing in order to retrieve our 500 rows as part of the the index-only scan node.
For each row returned in the final result, it will do 3 page read operations - first for the root page, then an intermediate/internal B-tree page, and lastly it will read the leaf page from where it will collect the reading (this is because we used the including clause when creating the index). I have marked these steps with 1, 2, 3 next to the arrows that represent descending down the tree.
Conclusion
After quite the journey, we've finally arrived at the fastest solution; meaning end of the line for this post. Note though that as they say, there is no free lunch - every additional index gives the database more work to do at every insert, so you will have to decide if it's worth it. Also, the results do depend on the data distribution. It's so fast because we in our dataset we have many readings for every meter, but things might be different in your setup.
References
The first time I heard about loose index scanning was from this SO answer by E. Brandstetter, next to this I had a look at other blogs tackling the subject.
- Loose Index Scan aka Skip Scan in PostgreSQL
- Select the Most Recent Record (of Many Items) With PostgreSQL
There is also a Postgres wiki entry on the topic.
Thanks for reading!
SOCIAL SHARE CARD GENERATOR