CODEWITHSUNDEEP
sqlpostgresql
WuBe
WuBe

Reputation: 41

PostgreSQL - How make simple query faster

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;

enter image description here

Upvotes: 3

Views: 172

Answers (2)

The Impaler
The Impaler

Reputation: 48750

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.

Upvotes: 5

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

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);

Upvotes: 4

Related Questions