pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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


>
>






view thread (9+ messages)

Message-ID: <39651.195.243.253.146.1081321601.squirrel@webmail.fh-oldenburg.de>
Permalink:  ../39651.195.243.253.146.1081321601.squirrel@webmail.fh-oldenburg.de/
Also on:    postgresql.org/message-id/39651.195.243.253.146.1081321601.squirrel@webmail.fh-oldenburg.de

 · 

reply

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