Re: Optimize date query for large child tables: GiST or GIN?

[David Jarvis <thangalin@xxxxxxxxx>]

Hi,

(An EXPLAIN ANALYSE would be better here). Look at the expected number of stations

"Nested Loop  (cost=0.00..994.94 rows=4046 width=4) (actual time=0.053..41.173 rows=78 loops=1)"
"  Join Filter: ((6371.009::double precision * sqrt((pow(radians(((c.latitude_decimal - s.latitude_decimal))::double precision), 2::double precision) + (cos((radians(((c.latitude_decimal + s.latitude_decimal))::double precision) / 2::double precision)) * pow(radians(((c.longitude_decimal - s.longitude_decimal))::double precision), 2::double precision))))) <= 25::double precision)"
"  ->  Index Scan using city_pkey1 on city c  (cost=0.00..6.27 rows=1 width=16) (actual time=0.014..0.016 rows=1 loops=1)"
"        Index Cond: (id = 5182)"
"  ->  Seq Scan on station s  (cost=0.00..321.08 rows=12138 width=20) (actual time=0.007..5.256 rows=12139 loops=1)"
"        Filter: ((s.elevation >= 0) AND (s.elevation <= 3000))"
"Total runtime: 41.235 ms"

expects to have to touch a large proportion of the measurement table, therefore it thinks that it will be fastest to do a seq scan. In actual fact, for 78 stations, the index would be faster, but for 4046 it wouldn't.

This is rather unexpected. I'd have figured it would use the actual number.
 

If you will be querying by season quite regularly, had you considered partitioning by season?

I have no idea what the "regular" queries will be. The purpose of the system is to open the data up to the public using a simple user interface so that they can generate their own custom reports. That user interface allows people to pick year intervals, day ranges, elevations, categories (temperature, precipitation, snow depth, etc.), and lat/long perimeter coordinates (encompassing any number of stations) or a city and radius.

Dave