PostgreSQL - trying to speed up query responses from view that has join and group by

Question

I have these two tables:

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

2. 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 id col (because it is used in the join to create the view)

In areas table I created:

• a spatial PostGIS index on the geom col
• 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.

Answer 1

where id <> 'ALL'

is not sargable. Try to rewrite with id IN (...) or id > 'ALL'

Create an index like this one :

CREATE INDEX X ON precipitaion (id, "time") INCLUDE (country, prec)

• umbe1987: thanks for the suggestion. My version of PostgreSQL (10) does not support INCLUDE. As for the other suggestion, I am having a look at it. Will come back to you, thanks.
• SQLpro: You can use an old bad index like : CREATE INDEX X ON precipitaion (id, "time", country, prec)
• umbe1987: what do you mean by "old bad" index?
• SQLpro: country and prec columns are not a part of the index key. But if you do not put thos two columns in the index, the query planer (optimizer) wont use it to speed your query. Placing those two columns as a part of the key, is not necessaray because there is no seek or DISTINCT/GROUP BY operator.... But in your version you do not have the choice. The refinement with the INCLUDE clause of indexes is that, those two columns can be put in the index but out of the index key, reducing the extra overhead and extra over cost of such "old bad index"...