The Following article triggered me to write this note:

Efficiently Finding the Latest Row per Group – Sweet Data Story

In that article, the author asked an AI assistant (Astra) to find the latest order for each shop. Several approaches were considered, but one proposal caught my eye.

The idea was to retrieve the latest order for the shop with the highest shop_id, then jump directly to the latest order for the next shop in the index.

At first glance, you may wonder what I am talking about. The following SQL should make the idea clearer:

WITH RECURSIVE last_orders AS (
    (
        -- Find the latest order for the shop with the highest shop_id
        SELECT x.*
        FROM startdbpg.tr_ord_big x
        ORDER BY x.shop_id DESC, x.ord_no DESC
        LIMIT 1
    )
    UNION ALL
    -- Skip the remaining orders for the current shop
    -- and find the latest order for the preceding shop
    SELECT n.*
    FROM last_orders p
    CROSS JOIN LATERAL (
        SELECT x.*
        FROM startdbpg.tr_ord_big x
        WHERE x.shop_id < p.shop_id
        ORDER BY x.shop_id DESC, x.ord_no DESC
        LIMIT 1
    ) n
)
SELECT t.*
FROM last_orders t
WHERE t.ord_dtm >= '2025-01-01'
  AND t.ord_dtm <  '2025-02-01';

After retrieving the latest order for one shop, the next iteration searches for an order beloing to a smaller shop_id(WHERE x.shop_id < p.shop_id).  PostgreSQL can reposition the index scan rather than reading all the intervening orders by using the clause ORDER BY ~ Limit 1.

This is a way of implementing a Loose Index Scan using a recursive CTE.

After reading Astra's proposal, I decided to apply the same idea to a SQL statement I had investigated in 2023. Here is the link to my previous note:

Extracting Historical Data 2

I won't repeat the test-data generation code here because it is available in the previous note.

1. The original query: LATERAL JOIN

In the previous note, I presented the following query to retrieve the latest status record for each piece of equipment:

select t20.equ_num, t20.equ_nm, t20.equ_sts_nm
     , t10.if_dt, t10.if_dt_seq, t10.if_sts_cd, t10.comt
from  equipments_test t20
join lateral
      (select t1.equ_num, t1.if_dt, t1.if_dt_seq, t1.if_sts_cd, t1.comt
         from   interface_test t1
        where t20.equ_num = t1.equ_num
        order by if_dt desc, if_dt_seq DESC
        fetch next 1 rows only
       ) t10
   on true;
 

The primary key of interface_test is defined on:

(equ_num, if_dt, if_dt_seq)

The index is particularly useful for the query. PostgreSQL can perform a backward index scan for each equipment number and stop as soon as it finds the first matching entry.

Here is the execution plan:

Nested Loop (actual time=0.064..1.734 rows=200.00 loops=1)
  Buffers: shared hit=803
  ->  Seq Scan on equipments_test t20 (actual time=0.010..0.021 rows=200.00 loops=1)
        Buffers: shared hit=3
  ->  Limit (actual time=0.008..0.008 rows=1.00 loops=200)
        Buffers: shared hit=800
        ->  Index Scan Backward using interface_test_pk on interface_test t1 (actual time=0.008..0.008 rows=1.00 loops=200)
              Index Cond: (equ_num = t20.equ_num)
              Index Searches: 200
              Buffers: shared hit=800
Planning Time: 0.106 ms
Execution Time: 1.757 ms

There are two important points to notice:

1. PostgreSQL uses a NL join, scanning the equipment table once.

2. For each equipment number, it performs a backward scan of the primary key index and stops after retrieving one tuple.

The inner index scan runs 200 times, once for each equipment record. Each execution retrieves just one row.

For this particular data set, the LATERAL strategy is already very efficient.

2. Applying Astra's recurive strategy

Now let's apply the same idea to the interface_test table.

Instead of driving the query from the equiqment master table, we start with the highest equipment number in the interface table. We then repeatedly retrieve the lates record for the next lower equiment number.

Here is the rewritten query:

with recursive w as (

  -- the anchor member retrieves the latest interface record for the highest equimment number
  (select equ_num, if_dt, if_dt_seq, if_sts_cd, comt
    from interface_test 
   order by equ_num desc, if_dt desc, if_dt_seq desc
  fetch next 1 rows only)
  union all

 -- the recursive member
  select b.equ_num, b.if_dt, b.if_dt_seq, b.if_sts_cd, b.comt
    from w 
    cross join lateral
      (select equ_num, if_dt, if_dt_seq, if_sts_cd, comt
        from interface_test a
       where a.equ_num < w.equ_num
       order by a.equ_num desc, a.if_dt desc, a.if_dt_seq desc
       fetch next 1 rows only
       ) b
    )
select t20.equ_num, t20.equ_nm, t20.equ_sts_nm
     , t10.if_dt, t10.if_dt_seq, t10.if_sts_cd, t10.comt
from  equipments_test t20
join w t10
  on  t20.equ_num = t10.equ_num;
 

The predicate a.equ_num < w.equ_num moves the search to the preceding equipment number. Because the index is ordered by equ_num, if_dt, and if_dt_seq, PostgreSQL can use the index to find the next equipment number and retrieve the latest record.

The process continues until no smaller equipment number can be found.

Here is the execution plan:

Hash Join (actual time=0.076..1.786 rows=200.00 loops=1)
  Hash Cond: (t10.equ_num = t20.equ_num)
  Buffers: shared hit=806
  CTE w
    ->  Recursive Union (actual time=0.017..1.607 rows=200.00 loops=1)
          Storage: Memory  Maximum Storage: 33kB
          Buffers: shared hit=803
          ->  Limit (actual time=0.016..0.017 rows=1.00 loops=1)
                Buffers: shared hit=4
                ->  Index Scan Backward using interface_test_pk on interface_test (actual time=0.015..0.015 rows=1.00 loops=1)
                      Index Searches: 1
                      Buffers: shared hit=4
          ->  Nested Loop (actual time=0.007..0.008 rows=0.99 loops=200)
                Buffers: shared hit=799
                ->  WorkTable Scan on w (actual time=0.000..0.000 rows=1.00 loops=200)
                ->  Limit (actual time=0.007..0.007 rows=0.99 loops=200)
                      Buffers: shared hit=799
                      ->  Index Scan Backward using interface_test_pk on interface_test a (actual time=0.007..0.007 rows=0.99 loops=200)
                            Index Cond: (equ_num < w.equ_num)
                            Index Searches: 200
                            Buffers: shared hit=799
  ->  CTE Scan on w t10 (actual time=0.018..1.663 rows=200.00 loops=1)
        Storage: Memory  Maximum Storage: 32kB
        Buffers: shared hit=803
  ->  Hash (actual time=0.053..0.054 rows=200.00 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 22kB
        Buffers: shared hit=3
        ->  Seq Scan on equipments_test t20 (actual time=0.008..0.026 rows=200.00 loops=1)
              Buffers: shared hit=3
Planning Time: 0.169 ms
Execution Time: 1.831 ms

There are three points worth examining in the plan:

First, PostgreSQL uses a recursive union to retrieve the lastest interface record for each equipment number.

Second, the recursive CTE avoids scanning every interface record.

There are 2 million rows in the interface_test table:

 - 200 equipment nubmers

 - 100 days of history per equipment

 - 100 interface records per day

The recursive approach performs one initial lookup and then one lookup per equipment number. The execution plan reports 200 index searches in the recursive member.

Third, PostgreSQL uses a hash join to retrieve the equipment information.

3. Comparing the two approaches

The two strategis are remarkably close in performance.

The LATERAL approach is slightly faster and accesses three fewer shared buffers (803 vs. 806). In this test, there is no performance reason to replace the original query with the recursive CTE.

However, the recursive CTE approach is worth keeping in mind.

Conclusion

When retrieving the lates record for each group, the choice of access strategy can make a difference.

A LATERAL join is an excellent option when a relatively small driving table is available. It allows PostgreSQL to perform a targeted index lookup for each group.

A recursive CTE offers another option. By repeatedly moving to the next group in a composite index, it can implement a Loose Index Scan and avoid processing every historical record.

Neither approach is universally superior. The right choice depends on the number of groups, the distribution of data, the availability of the driving table, and the indexes available.

The important point is not simply to retrieve the lates row for each group, but to make PostgreSQL reach that row with as little unnecessary work as possible.