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 D5302D1CCCA for ; Mon, 5 Apr 2004 15:54:30 +0000 (GMT) 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 26581-06 for ; Mon, 5 Apr 2004 12:54:45 -0300 (ADT) Received: from frodo.hserus.net (frodo.hserus.net [204.74.68.40]) by svr1.postgresql.org (Postfix) with ESMTP id C09C1D1C516 for ; Mon, 5 Apr 2004 12:54:14 -0300 (ADT) Received: from concord.pspl.co.in ([202.54.11.72]:64379 helo=frodo.hserus.net) by frodo.hserus.net with asmtp (Cipher TLSv1:DHE-RSA-AES256-SHA:256) (Exim 4.31 #0) id 1BAWR0-000Nty-GP by authid with plain; Mon, 05 Apr 2004 21:24:51 +0530 Message-ID: <40718146.1010506@frodo.hserus.net> Date: Mon, 05 Apr 2004 21:24:46 +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 Cc: pgsql-performance@postgresql.org Subject: Re: performance comparission postgresql/ms-sql server References: <48932.195.243.253.146.1081179099.squirrel@webmail.fh-oldenburg.de> In-Reply-To: <48932.195.243.253.146.1081179099.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/38 X-Sequence-Number: 6338 Heiko Kehlenbrink wrote: > hi list, > > i want to convince people to use postgresql instead of ms-sql server, so i > set up a kind of comparission insert data / select data from postgresql / > ms-sql server > > the table i use was pretty basic, > > id bigserial > dist float8 > x float8 > y float8 > z float8 > > i filled the table with a function which filled x,y,z with incremental > increasing values (1,2,3,4,5,6...) and computing from that the dist value > for every tupel (sqrt((x*x)+(y*y)+(z*z))). > > this works fine for both dbms > > postgresql needs 13:37 min for 10.000.000 tupel, > ms-sql needs 1:01:27 h for 10.000.000 tupel. > > so far so good. > > i attached an index on the dist row and started to query the dbs with > scripts which select a serial row of 100.000,200.000,500.000 tupels based > on the dist row. > i randomizly compute the start and the end distance and made a "select > avg(dist) from table where dist > startdist and dist < enddist" Some basics to check quickly. 1. vacuum analyze the table before you start selecting. 2. for slow running queries, check explain analyze output and find out who takes maximum time. 3. Check for typecasting. You need to typecast the query correctly e.g. select avg(dist) from table where dist >startdist::float8 and dist