[postgis-users] ST_Overlaps and performance

[Nicklas Avén]

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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>