From: Shridhar Daithankar <shridhar@frodo.hserus.net>
To: Heiko Kehlenbrink <Heiko.Kehlenbrink@vermes.fh-oldenburg.de>
To: Performance <pgsql-performance@postgresql.org>
Subject: Re: performance comparission postgresql/ms-sql server
Date: Tue, 06 Apr 2004 20:11:15 +0530
Message-ID: <4072C18B.1030600@frodo.hserus.net> (raw)
In-Reply-To: <58199.195.243.253.146.1081238266.squirrel@webmail.fh-oldenburg.de>
References: <48932.195.243.253.146.1081179099.squirrel@webmail.fh-oldenburg.de>
<40718146.1010506@frodo.hserus.net>
<56755.195.243.253.146.1081233367.squirrel@webmail.fh-oldenburg.de>
<40725BD1.3050008@frodo.hserus.net>
<58199.195.243.253.146.1081238266.squirrel@webmail.fh-oldenburg.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: shridhar@frodo.hserus.net, Heiko.Kehlenbrink@vermes.fh-oldenburg.de
Subject: Re: performance comparission postgresql/ms-sql server
In-Reply-To: <4072C18B.1030600@frodo.hserus.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox