Heiko Kehlenbrink wrote:
>>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.
> would be the last option till we are runing other applications on that 7.2
> system
I can understand..
>>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.
> a sequential scann gives me the following result:
>
> HKehlenbrink@lin0493l:~> time 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=1193714.43..1193714.43 rows=1 width=8) (actual
> time=166718.54..166718.54 rows=1 loops=1)
> -> Seq Scan on massive2 (cost=0.00..1192478.00 rows=494573 width=8)
> (actual time=3233.22..165576.40 rows=499999 loops=1)
> Total runtime: 166733.73 msec
Certainly bad and not an option.. I can't think of anything offhand to speed
this up..
Shridhar