How to use St_intersects with different geometry type

Question

I try to run this really simple spatial query with PostgreSQL+Postgis extension.

CASE 1:

SELECT a.id, b.id 
FROM PolygonLayer1 a, PolygonLayer2 b
WHERE St_intersects(a.geom,st_centroid(b.geom))

CASE 2:

SELECT a.id, b.id 
FROM PolygonLayer1 a, PolygonLayer2 b
WHERE St_intersects(st_centroid(a.geom),b.geom) -- This time I get the centroid of PolygonLayer1

Both PolygonLayer1 (~8000 features) and PolygonLayer2 (~10000 features) are polygons (or Multipolygons).

• The case 1 run in less than 10 seconds
• The case 2 never ends

I really don't understand the difference between the 2 cases.

So the question: What is the difference between the 2 cases ? How can I get a working query for both cases ?

Specification:

• If I run: EXPLAIN CASE1, I get a cost of ~20'000 "operations"

"Nested Loop  (cost=0.00..21878.84 rows=196 width=8)"
"  Join Filter: _st_intersects(st_centroid(a.geom), b.geom)"
"  ->  Seq Scan on superposition_bruit a  (cost=0.00..6089.04 rows=8804 width=728)"
"  ->  Index Scan using affectation_com_old_geom_gist on affectation_com_old b  (cost=0.00..1.53 rows=1 width=591)"
"        Index Cond: (st_centroid(a.geom) && geom)"

========================================================================

• If I run: EXPLAIN CASE2, I get a cost of ~40'000'000 "operations"

"Nested Loop  (cost=0.00..40530509.25 rows=196 width=8)"
"  Join Filter: ((a.geom && st_centroid(b.geom)) AND _st_intersects(a.geom, st_centroid(b.geom)))"
"  ->  Seq Scan on superposition_bruit a  (cost=0.00..6089.04 rows=8804 width=728)"
"  ->  Materialize  (cost=0.00..3813.98 rows=13332 width=591)"
"        ->  Seq Scan on affectation_com_old b  (cost=0.00..2744.32 rows=13332 width=591)"

Answer 1

Fast query results for ST_Intersects hinge on the fact that not every pair of inputs needs to be tested. PostGIS avoids testing every pair of geometries by implicitly testing the arguments to ST_Intersects with the bounding box intersection operator &&, so that only geometries whose bounding boxes intersect need to be passed to ST_Intersects. When your geometry columns are indexed, PostgreSQL can use the index to fetch only geometries that pass the && filter, significantly reducing the number of comparisons.

Here's the problem. The index provides the bounding boxes of a.geom and b.geom, but not ST_Centroid(a.geom). You and I know that whenever a.geom && b.geom is true, then ST_Centroid(a.geom) && b.geom must also be true, but PostgreSQL has no way to know this.

You can fix this by manually forcing an a.geom && b.geom comparison, which can take advantage of the index.

SELECT a.id, b.id 
FROM PolygonLayer1 a, PolygonLayer2 b
WHERE a.geom && b.geom AND ST_Intersects(ST_Centroid(a.geom), b.geom)

This doesn't explain why you're getting good performance in Case 1, because I have no idea.