Hallo If your query below is not the fastest of the different approaches I think you are going into a classical trap.
It is probably notthe query that takes a long time but the output of the result. Find out by not returning all the resulting geometries but instaed just count them. Instead of using "SELECT *" use "SELSECT count(*)" and compare the run-times. the calculation with the && operator (the index-based bbox selecton) is done behind the scenes when you use ST_Contains so it makes no sense that ST_Contains should be faster in ay other way that it returns fewer resulting polygons. HTH Nicklas On Wed, 2012-03-07 at 10:30 -0800, DrYSG wrote: > Not a great improvement in performance using: > > SELECT * > FROM portal.metadata as cat > WHERE cat.location && 'SRID=4326;POLYGON((-70.9433962 > 41.5384615,-72.5283019 41.5384615,-72.5283019 41.1538462,-70.9433962 > 41.1538462,-70.9433962 41.5384615))'::geometry; > > > 13:27:44 [SELECT - 2862 row(s), 37.360 secs] Result set fetched > ... 1 statement(s) executed, 2862 row(s) affected, exec/fetch time: > 37.360/0.073 sec [1 successful, 0 warnings, 0 errors] > > > > -- > View this message in context: > http://postgis.17.n6.nabble.com/ST-Overlaps-and-performance-tp4555465p4555662.html > Sent from the PostGIS - User mailing list archive at Nabble.com. > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
