PostGIS Performance: Intersection Predicates and Overlays

In this series, we talk about the many different ways you can speed up PostGIS. A common geospatial operation is to clip out a collection of smaller shapes that are contained within a larger shape. Today let's review the most efficient ways to query for things inside something else.

Frequently the smaller shapes are clipped where they cross the boundary, using the ST_Intersection function.

The naive SQL is a simple spatial join on ST_Intersects.

SELECT ST_Intersection(polygon.geom, p.geom) AS geom
  FROM parcels p
  JOIN polygon
    ON ST_Intersects(polygon.geom, p.geom);

When run on the small test area shown in the pictures, the query takes about 14ms. That’s fast, but the problem is small, and larger operations will be slower.

There is a simple way to speed up the query that takes advantage of the fact that boolean spatial predicates are faster than spatial overlay operations.

What?

• “Boolean spatial predicates” are functions like ST_Intersects and ST_Contains. They take in two geometries and return “true” or “false” for whether the geometries pass the named test.
• “Spatial overlay operations” are functions like ST_Intersection or ST_Difference that take in two geometries, and generate a new geometry based on the named rule.

Predicates are faster because their tests often allow for logical short circuits (once you find any two edges that intersect, you know the geometries intersect) and because they can make use of the prepared geometry optimizations to cache and index edges between function calls.

The speed-up for spatial overlay simply observes that, for most overlays there is a large set of features that can be added to the result set unchanged – the features that are fully contained in the clipping shape. We can identify them using ST_Contains.

Similarly, there is a smaller set of features that cross the border, and thus do need to be clipped. These are features that ST_Intersects but are not ST_Contains.

The higher performance function uses the faster predicates to filter the smaller shapes into two streams, one for intersection, and one for unchanged inclusion.

SELECT
  CASE
    WHEN ST_Contains(polygon.geom, p.geom) THEN p.geom
    ELSE ST_Intersection(polygon.geom, p.geom)
    END AS geom
  FROM parcels p
  JOIN polygon
    ON ST_Intersects(polygon.geom, p.geom);

Two predicates are used here, the ST_Intersects in the join clause ensures that only parcels that might participate in the overlay are fed into the CASE statement, where the ST_Contains predicate no-ops the parcels that do not cross the boundary.

When run against our tiny example, the query executes in just 9ms. Amazing that the difference is large enough to measure on such a small example.

Using CASE statement to combine predicates and overlays

The core idea here is to recognize that boolean spatial predicates like ST_Contains and ST_Intersects are computationally much faster than spatial overlay operations like ST_Intersection. The standard, but slow, approach clips all intersecting features. The optimized method uses a CASE statement and ST_Contains check to create a shortcut: if a smaller geometry is entirely contained within the larger clipping polygon, we return the geometry unchanged (a quick no-op) and completely bypass the slower ST_Intersection calculation.

You can apply this optimization pattern to any PostGIS work involving clipping, spatial joins, or overlays where you suspect a significant number of features might be fully contained within a boundary. By filtering and partitioning your geometries into "fully contained" (fast path) and "crossing the border" (slow path) streams, you ensure the expensive overlay operations are only executed when they are strictly necessary to clip the edges.

Need more PostGIS?
Join us this year on November 20 for PostGIS Day 2025, a free, virtual, community event about open source geospatial!