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