1

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

2
0
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)
5
  • 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.
    – umbe1987
    Sep 15 '20 at 12:09
  • You can use an old bad index like : CREATE INDEX X ON precipitaion (id, "time", country, prec)
    – SQLpro
    Sep 16 '20 at 13:06
  • what do you mean by "old bad" index?
    – umbe1987
    Sep 16 '20 at 13:10
  • 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"...
    – SQLpro
    Sep 17 '20 at 17:16
  • This is the reason why Microsoft introduce the concept of the INCLUDE clause in indexes in the SQL Server, there is about 12 yeras... And PG follows recently !
    – SQLpro
    Sep 17 '20 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.