Re: A way to optimize sql about the last temporary-related row

Sorry, my wrong settings on pc mail client.
Sorry again.
Agharta

Il ven 28 giu 2024, 19:51 Richard Welty <rwelty(at)salesium(dot)com> ha scritto:

> not really in direct response to this conversation, but is there any reason
> on the face of the planet why read receipts need to be sent to every single
> recipient of the mailing list?
>
> just saying,
> richard
>
>
>
> ---- On Fri, 28 Jun 2024 03:20:26 -0400 * <agharta82(at)gmail(dot)com
> <agharta82(at)gmail(dot)com>>* wrote ---
>
> HOO-HA! This is HUGE!
>
> Only 2.2 seconds on my data!!!! Amazing!
>
> distinct on (field) *followed by "*" *is a hidden gem!
>
> Thank you so much and thanks to everyone who helped me! Thank you very
> much!!
>
> Cheers,
>
> Agharta
>
>
>
>
> Il 27/06/24 6:16 PM, David Rowley ha scritto:
>
>
>
> On Fri, 28 Jun 2024, 3:20 am agharta82(at)gmail(dot)com, <agharta82(at)gmail(dot)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;
>
>
> 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
>
>
>
>
>