From: Heiko Kehlenbrink <Heiko.Kehlenbrink@vermes.fh-oldenburg.de>
To: pgsql-performance@postgresql.org
Subject: Re: performance comparission postgresql/ms-sql server
Date: Wed, 7 Apr 2004 09:06:41 +0200 (CEST)
Message-ID: <39651.195.243.253.146.1081321601.squirrel@webmail.fh-oldenburg.de> (raw)
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<enddist::float8..
>
> 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
>
>
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: Heiko.Kehlenbrink@vermes.fh-oldenburg.de
Subject: Re: performance comparission postgresql/ms-sql server
In-Reply-To: <39651.195.243.253.146.1081321601.squirrel@webmail.fh-oldenburg.de>
References: <39651.195.243.253.146.1081321601.squirrel@webmail.fh-oldenburg.de>
* 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