[mapserver-users] PostGIS performance

[david blasby]

Alexander,

You shouldnt make a bunch of layers with the same DATA statement - it
forces the DB to make the query for each layer in your mapfile.  In your case
you're doing 6* more work than necessary.

I'm a bit confused by your DB set up - is  "nut3" a normal table or is it
something else?
Same thing for "un3rt".

Usually you would only have to set up a bunch of CLASS statement within the
layer - like with a shapefile.  You're
probably having troubles because of some type of implicit joining of tables.

You should use the 3.7 mappostgis.c (I've already emailed it to you), and use
the
"advanced" DATA statement and do explict joins.

DATA "the_geom from () as foo using unique gid using SRID=6543"

replace "gid" with the name of the unique column in your data
replace "6543" with the SRID of your data
in the () put your SQL statement.  I sugest you explictly name your columns
like:
"SELECT the_geom, nut3.nurgcd as nurgcd, un3rt.unit as unit, gid FROM nut3"

DATA "the_geom from (SELECT the_geom, nut3.nurgcd as nurgcd, un3rt.unit as
unit,gid  FROM nut3) as foo using unique gid using SRID=6543"

If this doesnt help, you'll have to tell me what your table structure is.

dave
ps. make sure you have a spatial index on your table, and do a "vacuum
analyse" on the db.