[postgis-users] ST_SPHEROID_LENGTH SLOW

[Paul Ramsey]

I'm confused now.
what combinations of versions are fast and which ones are slow?
Can you do enough testing to narrow the problem down to either the
pgsql8.2->8.3 transition or the postgis1.3.2->1.3.3 transition?

P.

On Sat, Sep 27, 2008 at 3:44 PM, David Vaz <davidvaz at dcc.fc.up.pt> wrote:
> Actually the first time this happened was with 8.3+1.3.3, at that time
> debian still didn't have the postgis package for postgres 8.3 so I
> compiled postgis myself. Having that slow performance with that query I
> figured I had done something wrong with the compilation, so I stayed
> with 8.2+1.3.2, this week I tried again, given that debian testing
> already had postgis packages, but the speed was the same.
>
> Paul Ramsey wrote:
>> That's deeply odd, and worth investigating. Can you try 8.3+1.3.3, so
>> we can eliminate the PostGIS version as a variable first? Then I'll
>> see if I can duplicate the result on my system.
>>
>> P.
>>
>> On Sat, Sep 27, 2008 at 12:59 PM, David Vaz <davidvaz at dcc.fc.up.pt> wrote:
>>
>>> 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)
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>