[MS4W-Users] slow access to PostGIS layer

[G. Allegri]

I Jeff, thanks for your tips.
I've done some more tests:

1 - OGR works much better then POSTGIS. 2.7s the first against 7.2s the
latter. ????
2 - ogrinfo and psql shows that there aren't significative differences
between the simple SELECT statement, and the one with subquer

the gist index is there, but I've rebuilt it to be sure.
This results are quite alarming, aren't they? I didn't think OGR was so
faster then POSTGIS layers!

giovanni

2012/6/28 Jeff McKenna <jmckenna at gatewaygeomatics.com>

> 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/
>
>
>
>
> _______________________________________________
> MS4W-Users mailing list
> MS4W-Users at lists.maptools.org
> http://lists.maptools.org/mailman/listinfo/ms4w-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.maptools.org/pipermail/ms4w-users/attachments/20120628/00d6ae07/attachment.htm