I Jeff, thanks for your tips.<div>I've done some more tests:</div><div><br></div><div>1 - OGR works much better then POSTGIS. 2.7s the first against 7.2s the latter. ????</div><div>2 - ogrinfo and psql shows that there aren't significative differences between the simple SELECT statement, and the one with subquer</div>
<div><br></div><div>the gist index is there, but I've rebuilt it to be sure.</div><div>This results are quite alarming, aren't they? I didn't think OGR was so faster then POSTGIS layers!</div><div><br></div><div>
giovanni</div><div><br><div class="gmail_quote">2012/6/28 Jeff McKenna <span dir="ltr"><<a href="mailto:jmckenna@gatewaygeomatics.com" target="_blank">jmckenna@gatewaygeomatics.com</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="HOEnZb"><div class="h5">On 12-06-27 2:50 PM, G. Allegri wrote:<br>
> I'm facing very bad performances when accessing a polygonal layer on<br>
> PostGIS.<br>
> The layer is not that heavy and complex. It is made by 20 polygons with<br>
> about 4000 to 56000 vertices.<br>
><br>
> The raw query (SELECT the_geom FROM mytable), performed through the psql<br>
> console, fetches the results in about 185 ms.<br>
> Logging the layer with DEBUG 5 shows that iterating over the features<br>
> takes about 7.5 s.<br>
> The time between consecutive msPostGISLayerNextShape calls is about<br>
> 0.3/0.4 s.<br>
> Here is the log file: <a href="http://www.giovanniallegri.it/share/mspglog.txt" target="_blank">http://www.giovanniallegri.it/share/mspglog.txt</a><br>
><br>
> The sime times happen with ms4w 3.0.6 and 3.0.4_dev.<br>
> PostGIS is 1.5 on Postgresql 8.4, with no customized options.<br>
> I'm working on a Windows 7 machine, but the work should be deployed on a<br>
> Windows Server 2003.<br>
><br>
> I'm not using connection pooling, but I was facing similar performances<br>
> before disabling it.<br>
> giovanni<br>
<br>
</div></div>Hello Giovanni,<br>
<br>
I don't know your answer, but I'll give you my thoughts as I read your<br>
issue:<br>
<br>
- hmm I believe there is a trick to speed queries for Oracle<br>
connections, by adding EXTENT to the mapfile LAYER object (Mike would<br>
know more here). I don't think that is needed for PostGIS layers but I<br>
could be wrong<br>
- do you have the same problems for both CONNECTIONTYPE POSTGIS and<br>
CONNECTIONTYPE OGR for that layer?<br>
- so you are taking the exact query that MapServer uses and trying that<br>
through psql? (something like: SELECT __data__.cod_reg as<br>
gc_objid,__data__.the_geom as gc_geom,<br>
__data__.pop2001,__data__.cod_reg,__data__.regione FROM demo.regioni AS<br>
__data__) AS foo USING UNIQUE gc_objid USING SRID=23032)<br>
- also try that exact query with ogrinfo<br>
- load another small polygon shapefile into your PG, then try that with<br>
MapServer<br>
- make sure you have manually generated indexes for that table<br>
(<a href="http://www.postgis.org/documentation/manual-1.5/ch04.html#id2710658" target="_blank">http://www.postgis.org/documentation/manual-1.5/ch04.html#id2710658</a>)<br>
<br>
Others may have more thoughts. This may get you started.<br>
<br>
Good luck, it's tricky I know!<br>
<span class="HOEnZb"><font color="#888888"><br>
-jeff<br>
<br>
<br>
<br>
--<br>
Jeff McKenna<br>
MapServer Consulting and Training Services<br>
<a href="http://www.gatewaygeomatics.com/" target="_blank">http://www.gatewaygeomatics.com/</a><br>
<br>
<br>
<br>
<br>
_______________________________________________<br>
MS4W-Users mailing list<br>
<a href="mailto:MS4W-Users@lists.maptools.org">MS4W-Users@lists.maptools.org</a><br>
<a href="http://lists.maptools.org/mailman/listinfo/ms4w-users" target="_blank">http://lists.maptools.org/mailman/listinfo/ms4w-users</a><br>
</font></span></blockquote></div><br></div>