pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Shridhar Daithankar <shridhar@frodo.hserus.net>
To: Heiko Kehlenbrink <Heiko.Kehlenbrink@vermes.fh-oldenburg.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: performance comparission postgresql/ms-sql server
Date: Mon, 05 Apr 2004 21:24:46 +0530
Message-ID: <40718146.1010506@frodo.hserus.net> (raw)
In-Reply-To: <48932.195.243.253.146.1081179099.squirrel@webmail.fh-oldenburg.de>
References: <48932.195.243.253.146.1081179099.squirrel@webmail.fh-oldenburg.de>

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<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..

Post explain analyze for the queries if things don't improve.

  HTH

  Shridhar




view thread (9+ messages)  latest in thread

Message-ID: <40718146.1010506@frodo.hserus.net>
Permalink:  ../40718146.1010506@frodo.hserus.net/
Also on:    postgresql.org/message-id/40718146.1010506@frodo.hserus.net

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: shridhar@frodo.hserus.net, Heiko.Kehlenbrink@vermes.fh-oldenburg.de
  Subject: Re: performance comparission postgresql/ms-sql server
  In-Reply-To: <40718146.1010506@frodo.hserus.net>

* 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