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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
