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

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.