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@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.
