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

Hello everyone,
Sorry to bother you but I have a query that is driving me crazy.

I need to have the last valid record at a temporal level according to a 
specific parameter.

First some data:
Linux Rocky 8.10 environment, minimal installation (on VM KVM with 
Fedora 40).
Postgresql 16.3, installed by official Postgresql guide.
effective_cache_size = '1000 MB';
shared_buffers = '500 MB';
work_mem = '16MB';
The changes are deliberately minimal to be able to all to simulate the 
problem.

Table script:
CREATE TABLE test_table
(
  pk_id int NOT NULL,
  integer_field_1 int ,
  integer_field_2 int,
  datetime_field_1 timestamp,
  primary key (pk_id)
)

-- insert 4M records
insert into test_table(pk_id) select generate_series(1,4000000,1);

-- now set some random data, distribuited between specific ranges (as in 
my production table)
update test_table set
datetime_field_1 = timestamp '2000-01-01 00:00:00' + random() * 
(timestamp '2024-05-31 23:59:59' - timestamp '2000-01-01 00:00:00'),
integer_field_1 = floor(random() * (6-1+1) + 1)::int,
integer_field_2 = floor(random() * (200000-1+1) + 1)::int;


-- indexes
CREATE INDEX idx_test_table_integer_field_1 ON test_table(integer_field_1);
CREATE INDEX xtest_table_datetime_field_1 ON test_table(datetime_field_1 
desc);
CREATE INDEX idx_test_table_integer_field_2 ON test_table(integer_field_2);


--vacuum

vacuum full test_table;



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!!!
I was expecting about 2-3 seconds (according with my other queries in 
this table) but it seems that the xtest_table_datetime_field_1 index is 
not being used.

Do you think there is a way to optimize the query?

Thanks so much for the support,

Agharta

Hi,

You are right. Too quickly copy-paste on my part :-)

I take this opportunity to add a NOT insignificant detail.

Before executing the select query I clear the cache:

systemctl stop postgresql-16 && sync && echo 3 > /proc/sys/vm/drop_caches  &&  systemctl start postgresql-16

I need to get a performance result even if data is not in cache.

My best regards,

Agharta

On Thu, Jun 27, 2024 at 11:20 AM agharta82@gmail.com <agharta82@gmail.com> wrote:

[snip] 

-- insert 4M records
insert into test_table(pk_id) select generate_series(1,4000000,1);

-- now set some random data, distribuited between specific ranges (as in
my production table)
update test_table set
datetime_field_1 = timestamp '2000-01-01 00:00:00' + random() *
(timestamp '2024-05-31 23:59:59' - timestamp '2000-01-01 00:00:00'),
integer_field_1 = floor(random() * (6-1+1) + 1)::int,
integer_field_2 = floor(random() * (200000-1+1) + 1)::int;


-- indexes
CREATE INDEX idx_test_table_integer_field_1 ON test_table(integer_field_1);
CREATE INDEX xtest_table_datetime_field_1 ON test_table(datetime_field_1
desc);
CREATE INDEX idx_test_table_integer_field_2 ON test_table(integer_field_2);

Off-topic: save some resources by vacuuming before creating indices.

 

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;


Do you think there is a way to optimize the query?

Hi,

Thanks for you reply.

About syntax you're right, but I couldn't think of anything better :(((  I'm here for that too, to solve the problem in a fancy way, with your great support.

In practice, I need to get back a dataset with the last association (the most datatime recent record) for all the distinct entries of integer_field_2 based on filter:  integer_field_1 = 1

As said in another reply, the query needs to be performant even if data is not in cache (systemctl stop postgresql-16 && sync && echo 3 > /proc/sys/vm/drop_caches  &&  systemctl start postgresql-16).

Many thanks for your support.

Agharta

On Thursday, June 27, 2024, 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;


Do you think there is a way to optimize the query?

Write a lateral subquery to pick the first row of a descending ordered query? Using group to select ranked rows is both semantically wrong and potentially optimization blocking.

I’m going by the general query form and the “last row” aspect of the question.  I haven’t gone and confirmed your specific query can benefit from this approach. The window expression does give me pause.

David J.

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

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