[postgis-users] ST_SPHEROID_LENGTH SLOW

[David Vaz]

I am unable to find any significant difference in the configurations,
and the 2 datasets are in fact equal. It might be a slight difference in
the configuration or even in compilation of postgres, but I am no expert
in this so I need some help. I have attached both configuration and
output of 'SHOW ALL'. I hope someone can give light on this subject. To
me it seems the problem is on the Postgres side as you pointed out.

Mark Cave-Ayland wrote:
> 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.
>

-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: 8.2.all
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081007/39b75853/attachment.ksh>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: 8.2.conf
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081007/39b75853/attachment-0001.ksh>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: 8.3.all
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081007/39b75853/attachment-0002.ksh>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: 8.3.conf
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081007/39b75853/attachment-0003.ksh>