Hi, Rob.
I tried bumping the effective_cache_size. It made no difference.
My latest attempt at forcing PostgreSQL to use the indexes involved two
loops: one to loop over the stations, the other to extract the station data
from the measurement table. The outer loop executes in 1.5 seconds. The
inner loop does a full table scan for each record in the outer loop:
FOR station IN
SELECT
sc.station_id,
sc.taken_start,
sc.taken_end
FROM
climate.city c,
climate.station s,
climate.station_category sc
WHERE
c.id = city_id AND
earth_distance(
ll_to_earth(c.latitude_decimal,c.longitude_decimal),
ll_to_earth(s.latitude_decimal,s.longitude_decimal)) / 1000 <=
radius AND
s.elevation BETWEEN elevation1 AND elevation2 AND
s.applicable AND
sc.station_id = s.id AND
sc.category_id = category_id AND
extract(YEAR FROM sc.taken_start) >= year1 AND
extract(YEAR FROM sc.taken_end) <= year2
ORDER BY
sc.station_id
LOOP
RAISE NOTICE 'B.1. % % %', station.station_id, station.taken_start,
station.taken_end;
FOR measure IN
SELECT
extract(YEAR FROM m.taken) AS year,
avg(m.amount) AS amount
FROM
climate.measurement m
WHERE
* m.station_id = station.station_id AND
m.taken BETWEEN station.taken_start AND station.taken_end AND
m.category_id = category_id
* GROUP BY
extract(YEAR FROM m.taken)
LOOP
RAISE NOTICE ' B.2. % %', measure.year, measure.amount;
END LOOP;
END LOOP;
I thought that the bold lines would have evoked index use. The values used
for the inner query:
NOTICE: B.1. 754 1980-08-01 2001-11-30
When I run the query manually, using constants, it executes in ~25
milliseconds:
SELECT
extract(YEAR FROM m.taken) AS year,
avg(m.amount) AS amount
FROM
climate.measurement m
WHERE
m.station_id = 754 AND
m.taken BETWEEN '1980-08-01'::date AND '2001-11-30'::date AND
m.category_id = 7
GROUP BY
extract(YEAR FROM m.taken)
With 106 rows it should execute in ~2.65 seconds, which is better than the 5
seconds I get when everything is cached and a tremendous improvement over
the ~85 seconds from cold.
I do not understand why the below query uses a full table scan (executes in
~13 seconds):
SELECT
extract(YEAR FROM m.taken) AS year,
avg(m.amount) AS amount
FROM
climate.measurement m
WHERE
* m.station_id = station.station_id AND*
* m.taken BETWEEN station.taken_start AND station.taken_end AND*
* m.category_id = category_id*
GROUP BY
extract(YEAR FROM m.taken)
Moreover, what can I do to solve the problem?
Thanks again!
Dave
