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

[Puneet Kishor]

On Apr 28, 2012, at 8:20 AM, Stephen Woodbridge <woodbri at swoodbridge.com> wrote:

> 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.


All of the above is great advice. Additionally, implement a tilecache server; why redraw an image if it has already been drawn.

That said, while a db is going to be slower than the file system in many cases, there are many reasons why your db is performing so slowly. Thirty times slow is too much, and 30s for a query, no matter what query, is wrong. Benchmark your entire stack to find bottlenecks... Is the query itself taking that long? Is something else going wrong? Is the exact query that MapServer is performing actually using your indexes?

--
Puneet Kishor