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: performance comparission postgresql/ms-sql server
Date: Mon, 5 Apr 2004 17:31:39 +0200 (CEST)
Message-ID: <48932.195.243.253.146.1081179099.squirrel@webmail.fh-oldenburg.de> (raw)

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"

Did the same with a table with 50.000.000 tupel in ms-sql and postgres.

the outcome so far:

100.000 from 50.000.000:

postgres: 0.88 sec
ms-sql: 0.38 sec

200.000 from 50.000.000:

postgres: 1.57 sec
ms-sql: 0.54 sec

500.000 from 50.000.000:

postgres: 3.66 sec
ms-sql: 1.18 sec

i try a lot of changes to the postgresql.conf regarding  "Tuning
PostgreSQL for performance"
by
Shridhar Daithankar, Josh Berkus

which did not make a big diffrence to the answering times from postgresql.

i'm pretty fine with the insert time...

do you have any hints like compiler-flags and so on to get the answering
time from postgresql equal to ms-sql?

(btw both dbms were running on exactly the same hardware)

i use suse 8.1
      postgresql 7.2 compiled from the rpms for using postgis, but that is
anothe story...
      1.5 gig ram
      1.8 mhz intel cpu


every help welcome

best regards heiko






view thread (9+ messages)  latest in thread

Message-ID: <48932.195.243.253.146.1081179099.squirrel@webmail.fh-oldenburg.de>
Permalink:  ../48932.195.243.253.146.1081179099.squirrel@webmail.fh-oldenburg.de/
Also on:    postgresql.org/message-id/48932.195.243.253.146.1081179099.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: <48932.195.243.253.146.1081179099.squirrel@webmail.fh-oldenburg.de>
  References: <48932.195.243.253.146.1081179099.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