Performance problem with drawing several layers using Postgis

[Frans Knibbe]

Hello Andre,

At least you could leave PROCESSING "CLOSE_CONNECTION=DEFER" in the 
mapfile. Have a look at the thread about PostGIS performance where this 
is suggested:
http://lists.umn.edu/cgi-bin/wa?A2=ind0510&L=mapserver-users&D=0&I=-3&X=44C29F25DCFA63B744&Y=frans%40geodan.nl&P=70952

And do you have an index on the columns you use in the classification? 
I'm still not sure if this index will be used.. It would be interesting 
to see if this makes a difference.

Regards,

Frans


Andre Karp wrote:

>Hi,
>
>my problem sounds a bit obscure to me, but I couldn't figure it out on my
>own neither searching the archive, so maybe someone of you can give me an
>advice:
>
>I want to use the Mapserver (cgi) to draw a map in which the polygons are
>colored according to a datebase attribute (I think I have to store this
>attribute in MySQL or Postgre since it's changed quite often through the
>internet, several times a day). The user should be enabled to select one,
>several or all layers out of a set of 15 Layers. Every layer contains the
>same geometry: about 400 Polygons, not too complex; the corresponding
>shp-file is about 1000 kB, and drawing the shapefile with mapserver
>overlaying it 15 times takes less than a second.
>First I tried accessing the attribute data which is stored in a
>MySQL-Database via the OGR/ODBC driver, and performance was very weak (app.
>10 sec.)(the join fields where indexed). Then I tried by moving my data
>completly into a Postgis-based system (I also built a spatial index):
>performace got better, but it still takes roughly 5 sec to get a map, just
>drawing 15 Postgis-stored layers without any filtering or table joins and so
>on. The application runs on a P3-800 Mhz with 512 MB RAM and Windows
>2000/Apache System (well, not really fast, but should be sufficient since it
>has to handle only one request a time?).
>
>I would greatly appreciate if someone could give me an idea what could be
>wrong with my application - thanks a lot in advance!
>
>Here the relevant parts of my Mapfile (using OGR/ODBC):
>
>LAYER
>  NAME "Layer1"
>  TYPE POLYGON
>  CONNECTIONTYPE OGR
>  CONNECTION "test.shp"
>  DATA "SELECT * FROM test LEFT JOIN 'ODBC:user at testdb'.tbl_reg_cat ON
>test.ET_ID = tbl_reg_cat.et_id"
>  PROCESSING "CLOSE_CONNECTION=DEFER"
>  STATUS ON
>  ...
>
>and this using PostGIS:
>
>LAYER
>  NAME "Layer1"
>  TYPE POLYGON
>  CONNECTIONTYPE postgis
>  CONNECTION "user=test1 password=pw1 dbname=test_pg host=localhost"
>  DATA "the_geom from test"
>  STATUS ON
>    CLASS
>    ...
>
>Regards,
>
>Andre Karp
>
>
>  
>