[EMAIL PROTECTED]:~> psql -d test -c 'explain analyse select avg(dist) from massive2 where dist > (1000000*sqrt(3.0))::float8 and dist < (1500000*sqrt(3.0))::float8;' NOTICE: QUERY PLAN:
Aggregate (cost=14884.61..14884.61 rows=1 width=8) (actual time=3133.24..3133.24 rows=1 loops=1) -> Index Scan using massive2_dist on massive2 (cost=0.00..13648.17 rows=494573 width=8) (actual time=0.11..2061.38 rows=499999 loops=1) Total runtime: 3133.79 msec
EXPLAIN
seems to me that most time was needed for the index scanning...
Hmm... I would suggest if you are testing, you should try 7.4.2. 7.4 has some good optimisation for hash agregates though I am not sure if it apply to averaging.
Also try forcing a seq. scan by turning off index scan. I guess index scan for so many rows is not exactly good thing even if tuple size if pretty small.
Shridhar
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
