I was told to try OVERLAPS instead of checking years. The query is now:
SELECT
extract(YEAR FROM m.taken) AS year,
avg(m.amount) as amount
FROM
climate.city c,
climate.station s,
climate.station_category sc,
climate.measurement m
WHERE
c.id = 5148 AND
earth_distance(
ll_to_earth(c.latitude_decimal,c.longitude_decimal),
ll_to_earth(s.latitude_decimal,s.longitude_decimal)) / 1000 <= 30 AND
s.elevation BETWEEN 0 AND 3000 AND
s.applicable = TRUE AND
sc.station_id =
s.id AND
sc.category_id = 7 AND
(sc.taken_start, sc.taken_end) OVERLAPS ('1900-01-01'::date, '2009-12-31'::date) AND m.station_id =
s.id AND
m.taken BETWEEN sc.taken_start AND sc.taken_end AND
m.category_id = sc.category_id
GROUP BY
extract(YEAR FROM m.taken)
ORDER BY
extract(YEAR FROM m.taken)
25 seconds from cold, no full table scan:
Much better than 85 seconds, but still an order of magnitude too slow.
I was thinking of changing the
station_category table to use the measurement table's primary key, instead of keying off date, as converting the dates for comparison strikes me as a bit of overhead. Also, I can get remove the "/ 1000" by changing the Earth's radius to kilometres (from metres), but a constant division shouldn't be significant.
I really appreciate all your patience and help over the last sixteen days trying to optimize this database and these queries.
Dave