Re: performance comparission postgresql/ms-sql server

hi shridhar,

> 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.

was done,

> 2. for slow running queries, check explain analyze output and find out
who takes
> maximum time.

hkehlenbrink(at)lin0493l:~> psql -d test -c 'explain analyse select avg(dist)
from massive2 where dist > (1000000*sqrt(3.0))::float8 and dist <
(1500000*sqrt(3.0))::float8;'
NOTICE: QUERY PLAN:

Aggregate (cost=14884.61..14884.61 rows=1 width=8) (actual
time=3133.24..3133.24 rows=1 loops=1)
-> Index Scan using massive2_dist on massive2 (cost=0.00..13648.17
rows=494573 width=8) (actual time=0.11..2061.38 rows=499999 loops=1) Total
runtime: 3133.79 msec

EXPLAIN

seems to me that most time was needed for the index scanning...

> 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..
>
nope, the dist row is float8 and the query-borders are float8 too, also
the explain says that an index scann was done.

> Post explain analyze for the queries if things don't improve.
>
see above..

> HTH
>
> Shridhar
>
best regards
heiko

>
>