[MS4W-Users] slow access to PostGIS layer

[Jeff McKenna]

On 12-06-27 2:50 PM, G. Allegri wrote:
> I'm facing very bad performances when accessing a polygonal layer on
> PostGIS.
> The layer is not that heavy and complex. It is made by 20 polygons with
> about 4000 to 56000 vertices.
> 
> The raw query (SELECT the_geom FROM mytable), performed through the psql
> console, fetches the results in about 185 ms.
> Logging the layer with DEBUG 5 shows that iterating over the features
> takes about 7.5 s.
> The time between consecutive msPostGISLayerNextShape calls is about
> 0.3/0.4 s.
> Here is the log file: http://www.giovanniallegri.it/share/mspglog.txt
> 
> The sime times happen with ms4w 3.0.6 and 3.0.4_dev.
> PostGIS is 1.5 on Postgresql 8.4, with no customized options.
> I'm working on a Windows 7 machine, but the work should be deployed on a
> Windows Server 2003.
> 
> I'm not using connection pooling, but I was facing similar performances
> before disabling it.
> giovanni

Hello Giovanni,

I don't know your answer, but I'll give you my thoughts as I read your
issue:

- hmm I believe there is a trick to speed queries for Oracle
connections, by adding EXTENT to the mapfile LAYER object (Mike would
know more here).  I don't think that is needed for PostGIS layers but I
could be wrong
- do you have the same problems for both CONNECTIONTYPE POSTGIS and
CONNECTIONTYPE OGR for that layer?
- so you are taking the exact query that MapServer uses and trying that
through psql? (something like: SELECT __data__.cod_reg as
gc_objid,__data__.the_geom as gc_geom,
__data__.pop2001,__data__.cod_reg,__data__.regione FROM demo.regioni AS
__data__) AS foo USING UNIQUE gc_objid USING SRID=23032)
- also try that exact query with ogrinfo
- load another small polygon shapefile into your PG, then try that with
MapServer
- make sure you have manually generated indexes for that table
(http://www.postgis.org/documentation/manual-1.5/ch04.html#id2710658)

Others may have more thoughts.  This may get you started.

Good luck, it's tricky I know!

-jeff



-- 
Jeff McKenna
MapServer Consulting and Training Services
http://www.gatewaygeomatics.com/