On Thursday, June 27, 2024,
agharta82@gmail.com <
agharta82@gmail.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.