performance comparission postgresql/ms-sql server - Mailing list pgsql-performance

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