Performance problem with drawing several layers using Postgis

[Andre Karp]

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