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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 >>> [email protected] >>> http://postgis.refractions.net/mailman/listinfo/postgis-users >>> >>> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
