> 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.
> 2. for slow running queries, check explain analyze output and find out
> maximum time.
[EMAIL PROTECTED]:~> psql -d test -c 'explain analyse select avg(dist)
from massive2 where dist > (1000000*sqrt(3.0))::float8 and dist <
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
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
> This might still end up with sequential scan depending upon the plan.
> 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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?