I have these two tables:
precipitaion: store precipitation data at daily (freq = 'daily') and hourly (freq = 'hourly') frequencies for 7 months so far (I receive regular updates).
Column | Type
---------+----------------------------
id | varchar
country | varchar(2)
time | timestamp without time zone
freq | varchar
prec | float(4)
(2,481,069 rows)
areas: store multipolygon areas for all Europe down to municipality resolution.
Column | Type
---------+----------------------
id | varchar
country | varchar(2)
geom | geometry
(162,573 rows)
I need to create a view that combines the spatial and temporal values from these tables, so I created it this way:
create or replace view prec_geo
as select * from
(select
max(country) country,
id,
"time",
sum(prec) as prec,
from precipitaion
where id <> 'ALL'
group by "time", id) as prec,
areas.geom
where prec.id = areas.id and prec.country = areas.country;
The view now shows the columns and values I need to make further queries.
In particular, I need to make queries selecting different time and freq.
EXAMPLE:
select geom from prec_geo where "time" = '2020-03-13 00:00:00' and freq = 'daily' and country = 'it';
I created different indexes on my tables in the hope to speed up the queries.
In precipitation table I created:
- a combined index on multiple cols (
time(1),freq(2),coutry(3)) - a non unique index on
idcol (because it is used in the join to create the view)
In areas table I created:
- a spatial PostGIS index on the
geomcol - a combined index on multiple cols (
country(1),id(2))
I then ran VACUUM ANALYZE on both tables.
Still, the result of the query takes about 7 to 10 seconds, while I would need faster responses.
What would you suggest in order to increase performance?
Info on my setup: PostgreSQL10, PostGIS 2.5.
Best Answer
is not sargable. Try to rewrite with id IN (...) or id > 'ALL'
Create an index like this one :