[mapserver-users] performance problem with PostGIS layer

[Paul Ramsey]

You have done the right things, in terms of testing the query at the
commandline, building indexes, etc. Do remember to 'vacuum' your
database after bulk-loading and building an index. Do you have anything
special in your map definition, like a filter or something?

simon mercier wrote:
> 
> hi,
> I have performance problem with my PostGIS layer in mapserver.. It's
> slow... vvvery slow, when zoom in, zoom out and pan.  I'am new with
> PostgreSQL, PostGIS, MapServer and realy don't know where to look for
> this problem! I have a table of 392000 row of polygon with oid and GiST
> index . I have a debian server with a 933 CPU and 128 of RAM...
> 
> --I have good result when retrieving data with spatial SQL query :
> explain select count(*) from pclp_s where the_geom &&
> GeometryFromText('BOX3D(-72.15 46.43, -72.13 46.45)'::BOX3D,-1)
> Aggregate  (cost=8.94..8.94 rows=1 width=0)
>   ->  Index Scan using idx_geom_pclp_s on pclp_s  (cost=0.00..8.93
> rows=2 width=0)
> 
> --With the same box, (i.e. EXTENT -72.15 46.43 -72.13 46.45 in map
> file)  it take 80 sec for display image in IE with MapServer...
> 
> --A look on status server indicate postmaster take all CPU:
>  14:51:39 up 1 day, 23:31,  5 users,  load average: 0.69, 0.29, 0.10
> 60 processes: 56 sleeping, 4 running, 0 zombie, 0 stopped
> CPU states:  49.1% user,  44.7% system,   0.0% nice,   6.2% idle
> Mem:    126060K total,   122584K used,     3476K free,     1380K buffers
> 
> Swap:   248968K total,    19516K used,   229452K free,    71440K cached
> 
>   PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
>  7255 postgres  18   0  3920 3920  3236 R    85.6  3.1   0:13 postmaster
> 
>  7254 www-data   9   0  3080 3080  1876 S     0.0  2.4   0:00 mapserv
>  7253 root      11   0   992  992   776 R     0.3  0.7   0:00 top
> 
> It is a server harware problem or a soft setup problem? There is
> someting to do?
> 
> regard
> 
> --
> Simon Mercier
> Technicien en Géomatique
> Direction des technologies de l'information
> La Financière agricole du Quebec
> 5825, rue Saint-Georges
> Lévis, Québec,Canada G6V 4L2
> http://www.fadq.qc.ca/