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)