Boolean spatial predicates like ST_Intersects and ST_Contains are significantly faster than spatial overlay operations like ST_Intersection. When clipping geometries within a boundary, you can optimize performance by using a CASE statement to separate fully contained features (which don't need clipping) from boundary-crossing features (which do). This approach uses fast predicates to filter geometries into two streams, applying the expensive ST_Intersection only when necessary, resulting in measurable performance improvements even on small datasets.
Table of contents
Using CASE statement to combine predicates and overlays815 Impressions