[postgis-users] Another Question on PostGIS Performance

[Thomas Haechler]

Hi Dave,

Thanks for your answer. But I think there is a missunderstanding. The pg
column of the 2nd table is unique and there is an index on it (sorry
it's called "pg", not my choice). This is how the tables look like:

Table "veg"
gid | the_geom | pg  | ... |
----+----------+-----+-----+
   0| ...      |  12 | ... |
   1| ...      |   9 | ... |
   2| ...      |  40 | ... |
 ...| ...      | ... | ... |
6590| ...      |  33 | ... |

Indexes: veg_gid_index,  
         veg_gist_index
Check constraints: "$1" (srid(the_geom) = 4149)
                   "$2" ((geometrytype(the_geom) = 'MULTIPOLYGON'::text)
OR (NULL::geometry = the_geom))
 
--> CREATE INDEX veg_gist_index ON veg USING GIST ( the_geom
GIST_GEOMETRY_OPS ); 
--> CREATE INDEX veg_gid_index ON veg ( gid );  



Table "veg_atr" 
pg  | hs_zoller | ... |
----+-----------+-----+
   1| descr1    | ... |
   2| descr6    | ... |
   3| descr4    | ... |
 ...| descr..   | ... |
  40| descr6    | ... |

Indexes: veg_atr_pg_index 

--> CREATE INDEX veg_atr_pg_index ON veg_atr ( pg );

And the query string was: 
DATA "the_geom from ( select map.the_geom, data.hs_zoller, map.gid from
veg as map, veg_atr as data where map.pg = data.pg ) as foo using unique
gid"

Maybe the problem is the number of vertices like Paul said, since the
sum of points is in fact 432574. If I add another condition like: 
DATA "the_geom from ( select map.the_geom, data.hs_zoller, map.gid from
veg as map, veg_atr as data where map.pg = data.pg and data.pg<20) as
foo using unique gid",
the rendering time is about 9 secs and the sum of points is only 175772.

Regards,

Thomas



-- 

THOMAS HAECHLER

Geographic Information Systems Division
Institute of Geography
University of Zurich
Switzerland

thomas.haechler at geo.unizh.ch