Re: [PERFORM] performance comparission postgresql/ms-sql server

[Shridhar Daithankar]

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:

[EMAIL PROTECTED]:~> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster