Sorry, my wrong settings on pc mail client. Sorry again. Agharta Il ven 28 giu 2024, 19:51 Richard Welty <rwe...@salesium.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 * <aghart...@gmail.com > <aghart...@gmail.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 aghart...@gmail.com, <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 > > > > >
