Re: performance comparission postgresql/ms-sql server

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