DISTINCT ON redux

Two years ago, I had written a note saying that you should avoid using DISTINCT ON when query performance is essential:

https://www.postgresdba.com/bbs/board.php?bo_table=C05&wr_id=106&page=6

Regrettably, it turns out that my statement was incorrect. During an experiment on retrieving the most recent row from a table, I discoverd that utilizing the distinct on expression can be a better choice compared to using the traditional max() function.

https://www.postgresdba.com/bbs/board.php?bo_table=C05&wr_id=247

In the above note, I provided the code to create the sample dateset, so I won't repeat it here.
The initial query, provided by a poster on a Naver Cafe, aimed to extract the most recent status data of all 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   (
        select t1.equ_num, t1.if_dt, t1.if_dt_seq, t1.if_sts_cd, t1.comt
             , row_number() over(partition by t1.equ_num order by t1.if_dt desc, t1.if_dt_seq desc) as rnum
        from   interface_test t1
        where  1 = 1
       ) t10
     , equipments_test t20  
where  1 = 1
and    t10.rnum = 1
and    t20.equ_num = t10.equ_num ;

And I optimized the query by rewriting it like this:

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;

And I also introduced a technique to eliminate the sort operation in the original query:

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   (
        select t1.equ_num, t1.if_dt, t1.if_dt_seq, t1.if_sts_cd, t1.comt
             , row_number() over(partition by t1.equ_num
                               order by t1.equ_num desc, t1.if_dt desc, t1.if_dt_seq desc) as rnum
        from   interface_test t1
        where  1 = 1
       ) t10
     , equipments_test t20  
where  1 = 1
and    t10.rnum = 1
and    t20.equ_num = t10.equ_num ;

The following is the execution plan of the last query:

Nested Loop (actual time=0.052..491.942 rows=200 loops=1)
  Buffers: shared hit=31427 read=29093
  ->  Subquery Scan on t10 (actual time=0.048..491.052 rows=200 loops=1)
        Filter: (t10.rnum = 1)
        Buffers: shared hit=31027 read=29093
        ->  WindowAgg (actual time=0.047..490.984 rows=200 loops=1)
              Run Condition: (row_number() OVER (?) <= 1)
              Buffers: shared hit=31027 read=29093
              ->  Index Scan Backward using interface_test_pk on interface_test t1 (actual time=0.039..294.642 rows=2000000 loops=1)
                    Buffers: shared hit=31027 read=29093
  ->  Index Scan using equipments_test_pk on equipments_test t20 (actual time=0.003..0.003 rows=1 loops=200)
        Index Cond: (equ_num = t10.equ_num)
        Buffers: shared hit=400
Planning:
  Buffers: shared hit=8
Planning Time: 0.157 ms
Execution Time: 492.018 ms

Upon reviewing this execution plan, I was tempted to remove the WindowAgg operation since it consumes a significant portion of the total elapsed time.
The total execution time is 492 ms, and around 200 ms is spent on WindowAgg.

Consequently, I decided to remove the row_number() function and rewrote the inline view t10 as follows:

     
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 (SELECT a.equ_num, b.if_dt, b.if_dt_seq, b.if_sts_cd, b.comt
        FROM (select equ_num, max(array[if_dt::text,if_dt_seq::text]) as max_if_dt_seq
                from INTERFACE_TEST
               group by equ_num) a
             , interface_test B
       where a.equ_num = b.equ_num
         and a.max_if_dt_seq[1]::timestamp = b.if_dt
         and a.max_if_dt_seq[2]::numeric = b.if_dt_seq
       ) t10
   on t20.equ_num = t10.equ_num ;

In the inline view "a", I attempted to retrieve the most recent IF_DT, and IF_DT SEQ per EQU_NUM using the max() function. And then I joined those columns to the table INTERFACE_TEST to return other column values. In PostgreSQL, arrays are strongly typed, meaning that all elements within an array must have the same data type. So I had to use array[if_dt::text,if_dt_seq::text], resulting in a data type conversion.

Here is the execution plan:

Nested Loop (actual time=4.271..866.228 rows=200 loops=1)
  Join Filter: (interface_test.equ_num = t20.equ_num)
  Rows Removed by Join Filter: 39800
  Buffers: shared hit=10619 read=40
  ->  Seq Scan on equipments_test t20 (actual time=0.004..0.023 rows=200 loops=1)
        Buffers: shared hit=3
  ->  Materialize (actual time=0.021..4.313 rows=200 loops=200)
        Buffers: shared hit=10616 read=40
        ->  Nested Loop (actual time=4.264..860.686 rows=200 loops=1)
              Buffers: shared hit=10616 read=40
              ->  GroupAggregate (actual time=4.249..858.599 rows=200 loops=1)
                    Group Key: interface_test.equ_num
                    Buffers: shared hit=9816 read=40
                    ->  Index Only Scan using interface_test_pk on interface_test (actual time=0.004..180.380 rows=2000000 loops=1)
                          Heap Fetches: 0
                          Buffers: shared hit=9816 read=40
              ->  Index Scan using interface_test_pk on interface_test b (actual time=0.006..0.006 rows=1 loops=200)
                    Index Cond: ((equ_num = interface_test.equ_num)
                                  AND (if_dt = (((max(ARRAY[(interface_test.if_dt)::text, (interface_test.if_dt_seq)::text])))[1])::timestamp without time zone)
                                  AND (if_dt_seq = (((max(ARRAY[(interface_test.if_dt)::text, (interface_test.if_dt_seq)::text])))[2])::numeric))
                    Buffers: shared hit=800
Planning:
  Buffers: shared hit=12
Planning Time: 0.280 ms
Execution Time: 866.267 ms

Take note that most of the time was spent on the GroupAggregate node, which is much bigger than the WindowAgg node in the previous plan. This is due to the data type conversion in the array[].
So I decided to use the distinct on strategy instead because the distinct on expression does not require the data type conversion.
Here is the query utilizing the distinct on struct followed by its execution plan:

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 (select distinct on (t1.equ_num)  t1.equ_num, t1.if_dt, t1.if_dt_seq, t1.if_sts_cd, t1.comt
       from interface_test t1
      order by t1.equ_num desc, if_dt desc, if_dt_seq DESC
     ) t10
   on t20.equ_num = t10.equ_num ;

Nested Loop (actual time=481.199..484.194 rows=200 loops=1)
  Join Filter: (t20.equ_num = t10.equ_num)
  Rows Removed by Join Filter: 19900
  Buffers: shared hit=29498 read=30325
  ->  Seq Scan on equipments_test t20 (actual time=0.007..0.035 rows=200 loops=1)
        Buffers: shared hit=1 read=2
  ->  Materialize (actual time=0.000..2.409 rows=100 loops=200)
        Buffers: shared hit=29497 read=30323
        ->  Subquery Scan on t10 (actual time=0.046..480.890 rows=200 loops=1)
              Buffers: shared hit=29497 read=30323
              ->  Unique (actual time=0.044..480.782 rows=200 loops=1)
                    Buffers: shared hit=29497 read=30323
                          Buffers: shared hit=29497 read=30323
Planning Time: 0.098 ms
Execution Time: 484.226 ms

We can observe that the distinct on strategy was better idea than the query with the max() function. This contradicts my statement that you should avoid using DISTINCT ON for query performance in the previous article.
In the previous article, the max() function performed well because there was no data type conversion in the array[] function by accident.

Conclusion
When retrieving the most recent row, there is no iron law that the max() strategy is better than the distinct on strategy. Depending on the data specifics, we should choose the right strategy.