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

[EMAIL PROTECTED]:~> psql -d test -c 'explain analyse select avg(dist)
from massive2 where dist > (1000000*sqrt(3.0))::float8 and dist <

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


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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?


Reply via email to