Re: performance comparission postgresql/ms-sql server

Heiko Kehlenbrink wrote:

> hkehlenbrink(at)lin0493l:~> 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