[postgis-users] ST_SPHEROID_LENGTH SLOW

[David Vaz]

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?

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

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

EXPLAIN ANALYSE select length_spheroid(geom,'SPHEROID["WGS
84",6378137,298.257223563]') FROM test order by id limit 100;
                                                                     
QUERY
PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..17.44 rows=100 width=26054) (actual
time=1.852..588.394 rows=100 loops=1)
   ->  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)
 Total runtime: 588.495 ms
(3 rows)

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

EXPLAIN ANALYSE select length_spheroid(geom,'SPHEROID["WGS
84",6378137,298.257223563]') FROM test order by id limit 100;
                                                                        
QUERY
PLAN                                                                        

------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..8.74 rows=100 width=104429) (actual
time=182.734..59741.720 rows=100 loops=1)
   ->  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)
 Total runtime: 59741.897 ms
(3 rows)