public inbox for [email protected]  

help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: [email protected] <[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 08:33:18 -0700
Message-ID: <CAKFQuwb=EDKdxozCvkb1HqEcnBAzcCyNnOp=2Ywxyf2HdXV8ZA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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)  latest in thread

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: <CAKFQuwb=EDKdxozCvkb1HqEcnBAzcCyNnOp=2Ywxyf2HdXV8ZA@mail.gmail.com>

* 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