X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 15946D1B8DF for ; Tue, 6 Apr 2004 11:40:40 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 71071-05 for ; Tue, 6 Apr 2004 11:41:21 -0300 (ADT) Received: from frodo.hserus.net (frodo.hserus.net [204.74.68.40]) by svr1.postgresql.org (Postfix) with ESMTP id C1EABD1B88D for ; Tue, 6 Apr 2004 11:40:32 -0300 (ADT) Received: from concord.pspl.co.in ([202.54.11.72]:65087 helo=frodo.hserus.net) by frodo.hserus.net with asmtp (Cipher TLSv1:DHE-RSA-AES256-SHA:256) (Exim 4.31 #0) id 1BArlQ-00099m-4v by authid with plain; Tue, 06 Apr 2004 20:11:20 +0530 Message-ID: <4072C18B.1030600@frodo.hserus.net> Date: Tue, 06 Apr 2004 20:11:15 +0530 From: Shridhar Daithankar User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6b) Gecko/20031205 Thunderbird/0.4 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Heiko Kehlenbrink , Performance Subject: Re: performance comparission postgresql/ms-sql server 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> In-Reply-To: <58199.195.243.253.146.1081238266.squirrel@webmail.fh-oldenburg.de> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/61 X-Sequence-Number: 6361 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