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

[aghart...@gmail.com]

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


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

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

[Richard Welty]

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   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 mailto:aghart...@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;
 
 
 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

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

[agharta agharta]

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

Il ven 28 giu 2024, 19:51 Richard Welty  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 *  >* 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 aghart...@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;
>
>
> 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
>
>
>
>
>