[mapserver-users] performance problem with PostGIS layer

[Dave Blasby]

simon mercier wrote:
> --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)


I dont know why you'd be having performance problems.  The query that
mapserver sends to postgresql/PostGIS should take about the same amount
of time as the one you have above.  How long does the above query
actually take to perform (and how many rows are actually selected)?

There was a minor problem with WKB and polygons a while ago - make sure
you have the latest CVS version of PostGIS and Mapserver.

Do the individual polygons have a *HUGE* number of points in them?

Another strategy would be to create a new table ("pclp_s_small") with
just the results of the above query in it.  Ie.
CREATE TABLE pclp_s_small AS SELECT * FROM pclp_s WHERE the_geom && ...
Then use this table with mapserver and see if you get better
performance.  
If you do, then the performance problem has something to do with the
GiST indexing on pclp_s.  If the performance still stinks, it has
something to do with the communication between mapserver and
postgresql/PostGIS. 

dave