PostgreSQL - How make simple query faster

Question

I have a query that is done in 600ms. How to improve it - I'm thinking that line with date comparison is a key

select 
sensor_id,
sum(val) 
from tests 
where 
sensor_id in (34,35) --index on this column
and date_trunc('month', audit_date) = date_trunc('month', current_date)
group by sensor_id;

The Impaler · Accepted Answer · 2018-08-21 19:22:25Z

5

Just to complement Lukasz Szozda answer that I find very good.

In cases you cannot modify the SQL (for any reason) it's possible in PostgreSQL to create an index with the specific column and/or column expression combination. In your case:

create index ix1 on tests (sensor_id, date_trunc('month', audit_date));

With this index in place you can use your existing SQL untouched, and get high performance.

answered Aug 21, 2018 at 19:22

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bitifet Over a year ago

I was going to suggest the same solution not only as a workarround but as a, in a way. "better" solution (to just this concrete problem): Despite an index over audit_date column would be more generic (so preferrable because it is more likely to be used for other queries), an index over ` date_trunc(…)` will have much less different keys (so I guess it will be sligtly faster to traverse it, supposing it is a btree) and, more than that: having it will be used to test SINGLE equality instead of two inequalities (2 conditions, one being revense scan…).

Lukasz Szozda · Accepted Answer · 2018-08-21 19:14:27Z

4

You could make expression date_trunc('month', audit_date) SARGable:

select 
   sensor_id,
   sum(val) 
from tests 
where sensor_id in (34,35) --index on this column
  and audit_date >= cast(date_trunc('month', current_date) as date)
  and audit_date < cast(date_trunc('month', current_date) as date)
                   + interval '1 month'
group by sensor_id;

And create index:

CREATE INDEX idx ON tests(sensor_id, audit_date);

edited Aug 21, 2018 at 19:14

answered Aug 21, 2018 at 19:09

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

1 Comment

WuBe Over a year ago

Great. Time droped to 45 ms.

Collectives™ on Stack Overflow

PostgreSQL - How make simple query faster

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related