[postgis-users] Another Question on PostGIS Performance

[Thomas Haechler]

Paul, Dave,

sorry, as I said, I'm new to all this...

Since I rendered all the data, and based on shpdump I guess the mumber
of vertices might be about 450'000 (how can you tell exactly, btw.?).
The spatial area is Xmin=795000.000, Xmax=830000.000, Ymin=158000.000,
Ymax=200000.000 in Swiss coordinates ( ground area = 35x42km). The value
distribution of the pg column is from 1 - 40, and the veg table has 6591
rows. The hardware is a Sun Ultra 60/300. 

This is the output from EXPLAIN:

EXPLAIN ANALYZE SELECT
asbinary(force_collection(force_2d(the_geom)),'XDR'),gid::text 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 WHERE the_geom &&
setSRID('BOX3D(784993.862745 149994.224698,835019.524963
199999.514866)'::BOX3D, find_srid('','veg','the_geom') ) ;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..7.91 rows=1 width=44) (actual
time=16.08..12505.91 rows=6591 loops=1)
  ->  Index Scan using veg_gist_index on veg map  (cost=0.00..6.01
rows=1 width=40) (actual time=7.05..1758.70 rows=6591 loops=1)
  ->  Seq Scan on veg_atr data  (cost=0.00..1.40 rows=40 width=4)
(actual time=0.03..0.78 rows=40 loops=6591)
Total runtime: 12538.11 msec 

Maybe I should explain what I would like this to use for: I intended to
allow the user to do a dynamic query on some attribute tables and then
visualize the result in mapserver by drawing this new layer dynamically
on top of the other layers, with the class expressions set dynamically,
too. Like this, the user could prduce a map with a top layer that shows
for example all pine trees that grow in the montaneous vegetation level
or something similar.

Thank you for helping,

Thomas