[postgis-users] ST_SPHEROID_LENGTH SLOW

[David Vaz]

Mark Cave-Ayland wrote:
> Have you tried a pg_dump/re-initdb/pg_restore cycle on your 8.3
> installation? If the problem turns out to be either corruption or
> table/index bloat then this should immediately resolve it. Also just
> to confirm - where are you getting your debian packages from?
I have tried the pg_dump/re-initdb/pg_restore, and although You were
wright I was suffering from table/index bloat the time results are the same:

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..11.07 rows=100 width=36) (actual
time=219.032..59913.512 rows=100 loops=1)
   ->  Index Scan using test_pkey on tracker_track  (cost=0.00..1439.93
rows=13010 width=36) (actual time=219.029..59913.377 rows=100 loops=1)
 Total runtime: 59913.701 ms
(3 rows)

In 8.2 Total runtime was 588.495 ms

They are from the official debian "testing/lenny" repository, the 8.2
version is not available any more, but I can give you the .deb if you
want to try it out. But the 8.3 is the problematic one, and that is main
version on lenny.

Regards