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

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:
> On Thursday, June 27, 2024, agharta82(at)gmail(dot)com <agharta82(at)gmail(dot)com>
> 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.
>