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

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@gmail.com, <agharta82@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