On Thu, 20 May 2010, David Jarvis wrote:
I took out the date conditions: SELECT m.* FROM climate.measurement m WHERE m.category_id = 1 and m.station_id = 2043 This uses the station indexes:
Yes, because there is only one station_id selected. That's exactly what an index is for.
Then combined the selection of the station: The station index is no longer used, resulting in full table scans:
"Nested Loop (cost=0.00..994.94 rows=4046 width=4)" " 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)" " Index Cond: (id = 5182)" " -> Seq Scan on station s (cost=0.00..321.08 rows=12138 width=20)" " Filter: ((s.elevation >= 0) AND (s.elevation <= 3000))" I get a set of 78 rows returned in very little time.
(An EXPLAIN ANALYSE would be better here). Look at the expected number of stations returned. It expects 4046 which is a large proportion of the available stations. It therefore 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.
If you will be querying by season quite regularly, had you considered partitioning by season?
Matthew -- Geography is going places. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
