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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
