0

When I query with ST_Intersects or ST_Within I get the same result. I want to know which one to use for performance wise? Note: For points, there is no difference.

Using ST_Within:

EXPLAIN ANALYZE 
SELECT id, name,short_name,type FROM locations WHERE ST_Within('SRID=4326;POINT(9.1011853 49.5994825)',bounding_polygon)

QUERY PLAN

"Index Scan using locations_bounding_polygon_idx on locations  (cost=0.29..19727.17 rows=1 width=76) (actual time=0.326..1.389 rows=8 loops=1)"
"  Index Cond: (bounding_polygon ~ '0101000020E610000001AC448FCE3322401D03B2D7BBCC4840'::geometry)"
"  Filter: st_within('0101000020E610000001AC448FCE3322401D03B2D7BBCC4840'::geometry, bounding_polygon)"
"  Rows Removed by Filter: 18"
"Planning Time: 0.126 ms"
"Execution Time: 1.405 ms"

Using ST_Intersects:

EXPLAIN ANALYZE 
SELECT id, name,short_name,type FROM locations WHERE ST_Intersects(bounding_polygon, 'SRID=4326;POINT(9.1011853 49.5994825)') 

QUERY PLAN

"Index Scan using locations_bounding_polygon_idx on locations  (cost=0.29..33.30 rows=1 width=76) (actual time=0.270..1.102 rows=8 loops=1)"
"  Index Cond: (bounding_polygon && '0101000020E610000001AC448FCE3322401D03B2D7BBCC4840'::geometry)"
"  Filter: st_intersects(bounding_polygon, '0101000020E610000001AC448FCE3322401D03B2D7BBCC4840'::geometry)"
"  Rows Removed by Filter: 18"
"Planning Time: 0.231 ms"
"Execution Time: 1.116 ms"

ST_Intersects: https://postgis.net/docs/ST_Intersects.html If a geometry or geography shares any portion of space then they intersect.

ST_Within: https://postgis.net/docs/ST_Within.html Returns TRUE if geometry A is completely inside geometry B

2
  • 1
    ST_Intersects bools out earlier than other relationship functions. If in doubt, this is trivial to test. Bonus performance: ST_Subdivide your polygons. – geozelot Jan 27 at 7:37
  • 1
    I have to add: this is true for Point in Polygon, but not necesarily for other combinations; ST_Within checks for bbox containment (~), which may have a significant impact on index driven exclusion compared to bbox intersection (&&) used by ST_Intersects - despite the computationally more expensive true containment check. – geozelot Jan 27 at 11:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.