Subject: Re: A way to optimize sql about the last temporary-related row

From: "David G. Johnston" <[email-masked]>
Date: Thu, 27 Jun 2024 08:33:18 -0700

On Thursday, June 27, 2024, [email-masked] <[email-masked]> wrote:

>
> Now the query:
> explain (verbose, buffers, analyze)
> with last_table_ids as materialized(
>   select xx from (
>   select LAST_VALUE(pk_id) over (partition by integer_field_2 order by
> datetime_field_1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> xx
>   from test_table
>   where integer_field_1 = 1
>   and datetime_field_1 <= CURRENT_TIMESTAMP
>   ) ww group by ww.xx
>
> ),
> last_row_per_ids as (
>   select tt.* from last_table_ids lt
>   inner join test_table tt on (tt.pk_id = lt.xx)
>
> )
>
> select * /* or count(*) */ from last_row_per_ids;
>
>
> Do you think there is a way to optimize the query?

Write a lateral subquery to pick the first row of a descending ordered
query? Using group to select ranked rows is both semantically wrong and
potentially optimization blocking.

I’m going by the general query form and the “last row” aspect of the
question.  I haven’t gone and confirmed your specific query can benefit
from this approach. The window expression does give me pause.

David J.

---

From: "[email-masked]" <[email-masked]>
Date: Thu, 27 Jun 2024 17:51:42 +0200

Hi,

Thanks for you reply.

About syntax you're right, but I couldn't think of anything better :(((  
I'm here for that too, to solve the problem in a fancy way, with your 
great support.

In practice, I need to get back a dataset with the last association (the 
most datatime recent record) for all the distinct entries of 
integer_field_2 based on filter:  integer_field_1 = 1

As said in another reply, the query needs to be performant even if data 
is not in cache (systemctl stop postgresql-16 && sync && echo 3 > 
/proc/sys/vm/drop_caches  && systemctl start postgresql-16).

Many thanks for your support.

Agharta

Il 27/06/24 5:33 PM, David G. Johnston ha scritto:
>       select LAST_VALUE(pk_id) over (partition by integer_field_2
>     order by datetime_field_1 RANGE BETWEEN UNBOUNDED PRECEDING AND
>     UNBOUNDED FOLLOWING) xx
>       from test_table
> query? Using group to select ranked rows is both semantically wrong 
> and potentially optimization blocking.
>
> question.  I haven’t gone and confirmed your specific query can 
> benefit from this approach. The window expression does give me pause.
>
> David J.
>