public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Rowley <[email protected]>
To: [email protected]
Cc: PostgreSQL General <[email protected]>
Subject: Re: A way to optimize sql about the last temporary-related row
Date: Fri, 28 Jun 2024 04:16:24 +1200
Message-ID: <CAApHDvpvnw2FKbPxrk58F6vo2-S9d_ooTkwgsRoWTVjmjkc2dg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On Fri, 28 Jun 2024, 3:20 am [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;
>
>
> This query, on my PC, takes 46 seconds!!!
>

(Away from laptop and using my phone)

Something like:

select distinct on (integer_field_2) * from test_table where
integer_field_1 = 1 and datetime_field_1 <= CURRENT_TIMESTAMP order by
integer_field_2,datetime_field_1 desc;

Might run a bit faster.  However if it's slow due to I/O then maybe not
much faster.  Your version took about 5 seconds on my phone and my version
ran in 1.5 seconds.

It's difficult for me to check the results match with each query from my
phone. A quick scan of the first 10 or so records looked good.

If the updated query is still too slow on cold cache then faster disks
might be needed.

David

>

view thread (3+ 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: <CAApHDvpvnw2FKbPxrk58F6vo2-S9d_ooTkwgsRoWTVjmjkc2dg@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