[mapserver-users] Really slow map creation from POSTGIS DB

[Stephen Woodbridge]

On 4/28/2012 8:44 AM, Matt McClelland wrote:
> Hi All
>
> I have a set of contours in a SHP file
> If I point my map file to the SHP file I can generate a WMS image in
> under 1 second
> Shp file is just shy of 200MB
>
> I then use shp2pgsql to load the same dataset into the DB
> I added an index for the_geom
> And am requesting the data in the mapfile using the same EPSG
>
> The map draws correclty but is now taking about 30secs when using the DB.
>
> Any ideas on how to make it faster.

Simplify the data for the that zoom level.

If you are all the way zoomed out and drawing all the data, then the 
shapefile will be fastest. An index does not help if you are drawing all 
the data as you need to do a full table scan anyway to get all the 
records. It is simply faster to read 200MB from disk than from the 
database because the database has to fetch the same data and may have to 
jump through some hoops to get it out of extended storage, then it has 
to encode the data and send it over the connection and the client has to 
decode the data.

Do you really need all the resolution of the data at this scale? If not 
create another column and simplify the geometry into that column and set 
up your mapfile to query the simplified data at this zoom and to query 
the detailed data as you zoom in.

-Steve W