Using RTREE in spatial joins

[rafapereirabr]

Hey all, I just wanted clarify something about the documentation.

This post from 2025/08 says R-Tree indexing would work fine and improve the performance of spatial join operations. So

SELECT v1.CNTR_ID, v1.NAME_ENGL, v2.CNTR_ID, v2.ISO3_CODE, ST_AsText(v1.geometry) AS geometry
FROM brazil v1
LEFT JOIN countries v2
    ON ST_Intersects(v1.geometry, v2.geometry)

However, this page with the documentation about R-Tree indexing says "The R-tree index will only be used to perform "index scans" when the table is filtered (using a WHERE clause)", which susggest I should use:

SELECT v1.CNTR_ID, v1.NAME_ENGL, v2.CNTR_ID, v2.ISO3_CODE, ST_AsText(v1.geometry) AS geometry
FROM brazil v1, countries v2
    WHERE ST_Intersects(v1.geometry, v2.geometry)

Could you please clarify whether the best approach here? Would R-Tree indexing improve the performance of spatial join operations?

[ppasquet]

Check this blog entry https://duckdb.org/2025/08/08/spatial-joins

[rafapereirabr]

Hi @ppasquet . thanks. This link you shared is the first link I pointed to in my first comment. All the examples in that link use JOIN ... ON ...

SELECT v1.CNTR_ID, v1.NAME_ENGL, v2.CNTR_ID, v2.ISO3_CODE, ST_AsText(v1.geometry) AS geometry
FROM brazil v1
JOIN countries v2
    ON ST_Intersects(v1.geometry, v2.geometry)

However, the second link, which is the documentation for R-Tree indexes, says explicitly:

• "The R-tree index will only be used to perform "index scans" when the table is filtered (using a WHERE clause) with one of the following spatial predicate functions (as they all imply intersection): ST_Equals, ST_Intersects, ST_Touches, ST_Crosses, ST_Within, ST_Contains, ST_Overlaps, ST_Covers, ST_CoveredBy, ST_ContainsProperly."

something along the lines of:

SELECT v1.CNTR_ID, v1.NAME_ENGL, v2.CNTR_ID, v2.ISO3_CODE, ST_AsText(v1.geometry) AS geometry
FROM brazil v1, countries v2
    WHERE ST_Intersects(v1.geometry, v2.geometry)

Hence my question. What approach gets the most performance gain from R-Tree indexing?

• JOIN ... ON ST_Intersects(tbl1.geometry, tbl1.geometry)
• WHERE ST_Intersects(tbl1.geometry, tbl1.geometry)

[Maxxen]

Hello!

JOIN ... ON ST_Intersects(tbl1.geometry, tbl1.geometry)

This may use the SPATIAL_JOIN operator, regardless if there is a r-tree index defined on the table or not. As explained in the blog post on spatial joins, the SPATIAL_JOIN operator creates a temporary internal r-tree data structure to perform the join, it does not use an existing index created on the table.

WHERE ST_Intersects(tbl1.geometry, tbl1.geometry)

This won't be optimized if there is a r-tree defined on the table either. The scan/filtering optimization is only performed if there is an r-tree index defined on the table and one of the arguments is a constant (i.e. not a column). If you had an r-tree index defined on the table and issued a query like ... WHERE ST_Intersects(tbl1.geometry, ST_GeomFromText('POLYGON((...))'); the r-tree index scan may be used since the second argument to ST_intersects is now constant and known at query-planning time.

You can verify which optimizations are applied by inspecting the logical plan using the EXPLAIN statement.