public inbox for [email protected]
help / color / mirror / Atom feedFrom: [email protected] <[email protected]>
To: David G. Johnston <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: A way to optimize sql about the last temporary-related row
Date: Thu, 27 Jun 2024 17:51:42 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKFQuwb=EDKdxozCvkb1HqEcnBAzcCyNnOp=2Ywxyf2HdXV8ZA@mail.gmail.com>
References: <[email protected]>
<CAKFQuwb=EDKdxozCvkb1HqEcnBAzcCyNnOp=2Ywxyf2HdXV8ZA@mail.gmail.com>
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, [email protected] <[email protected]>
> 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.
>
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: A way to optimize sql about the last temporary-related row
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox