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

[Stephen Woodbridge]

On 4/28/2012 9:44 AM, Puneet Kishor wrote:
> 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?

In pgAdmin or from the psql commandline, how long does it take to do:

select * from yourtable;

Do you have the exact query that mapserver is making? You can get it by 
adding to the MAP object

DEBUG 10
CONFIG MS_ERRORFILE "stderr"

then looking in the apache error_log.

-Steve