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 C1646D1BB16 for ; Wed, 7 Apr 2004 04:15:30 -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 50922-01 for ; Wed, 7 Apr 2004 04:15:28 -0300 (ADT) Received: from ol-mailrelay-01.fh-oldenburg.de (ol-mailrelay-01.fh-oldenburg.de [139.13.186.99]) by svr1.postgresql.org (Postfix) with ESMTP id 74853D1BAD6 for ; Wed, 7 Apr 2004 04:15:25 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by ol-mailrelay-01.fh-oldenburg.de (Postfix) with ESMTP id 148FA38C3F7 for ; Wed, 7 Apr 2004 09:06:42 +0200 (CEST) Received: from ol-mailrelay-01.fh-oldenburg.de ([127.0.0.1]) by localhost (ol-mailrelay-01 [127.0.0.1]) (amavisd-new, port 10023) with ESMTP id 24456-03 for ; Wed, 7 Apr 2004 09:06:41 +0200 (CEST) Received: from webmail.fh-oldenburg.de (ol-mailrelay-01.fh-oldenburg.de [139.13.186.99]) by ol-mailrelay-01.fh-oldenburg.de (Postfix) with SMTP id B95F538C3F4 for ; Wed, 7 Apr 2004 09:06:41 +0200 (CEST) Received: from 195.243.253.146 (SquirrelMail authenticated user kehlenbh) by webmail.fh-oldenburg.de with HTTP; Wed, 7 Apr 2004 09:06:41 +0200 (CEST) Message-ID: <39651.195.243.253.146.1081321601.squirrel@webmail.fh-oldenburg.de> Date: Wed, 7 Apr 2004 09:06:41 +0200 (CEST) Subject: Re: performance comparission postgresql/ms-sql server From: "Heiko Kehlenbrink" To: pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.2 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 Importance: Normal X-Virus-Scanned: by amavisd-new X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/73 X-Sequence-Number: 6373 hi shridhar, > 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. was done, > 2. for slow running queries, check explain analyze output and find out who takes > maximum time. hkehlenbrink@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... > 3. Check for typecasting. You need to typecast the query correctly e.g. > > select avg(dist) from table where dist >startdist::float8 and > dist > This might still end up with sequential scan depending upon the plan. but if > index scan is picked up, it might be plenty fast.. > nope, the dist row is float8 and the query-borders are float8 too, also the explain says that an index scann was done. > Post explain analyze for the queries if things don't improve. > see above.. > HTH > > Shridhar > best regards heiko > >