[postgis-users] ST_SPHEROID_LENGTH SLOW

[Mark Cave-Ayland]

Hi David,

> Hi,
> 
> I am a debian user, several months ago debian testing introduced
> postgresql-8.3, and later postgis 1.3 for postgresql-8.3. The problem I
> am facing is that length_spheroid is much slower in postgres-8.3 from
> under 1s to about 60s (See Bellow). I have google-it without any luck.
> Does anyone have any idea on why this happens?

Firstly it is not the length_spheroid function that is being slow here - 
the time is disappearing into the index scan on your primary key.

> Both databases are equal, tested in same machine, with different
> installations to avoid software versions conflicts.

Something doesn't quite ring true here. Looking at your results below I 
see this:

> FIRST:
> postgresql-8.2 (version 8.2.7-2+b1) with postgis (version 1.3.2-1)

(cut)

>    ->  Index Scan using test_pkey on test  (cost=0.00..2268.52
> rows=13010 width=26054) (actual time=1.850..588.298 rows=100 loops=1)
                    ^^^^^
(cut)

> SECOND:
> postgresql-8.3 (version 8.3.3-1) with postgis (version 1.3.3-3)

>    ->  Index Scan using test_pkey on test  (cost=0.00..1137.11
> rows=13010 width=104429) (actual time=182.732..59741.572 rows=100 loops=1)
                    ^^^^^^
The width parameter gives what PostgreSQL thinks is the average size of 
a row for this scan, and the two are clearly different - are you sure 
you have the same dataset on both systems?

Also you haven't mentioned anything about comparing settings in 
postgresql.conf - are they the same, and are you definitely using 
autovacuum on both systems? If not, a "VACUUM FULL test; REINDEX TABLE 
test" may help if you are suffering from table/index bloat.


HTH,

Mark.

-- 
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063