What is difference between the boolean true / false ST_Intersects and ST_Contains and the overlay options of ST_Intersection and ST_Difference? Also, combining these two ideas can get you really fast queries for geometries fully contained inside areas.

CrunchyData's platform is a central hub for PostgreSQL professionals and database administrators, offering insights into PostgreSQL deployment, administration, and optimization. Through articles, tutorials, and case studies, CrunchyData offers insights into PostgreSQL features, replication strategies, and high availability architectures. Readers can learn about database performance tuning, backup and recovery procedures, and security best practices to ensure the reliability and security of their PostgreSQL deployments.

Crunchy Data

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.

PostGIS Performance: Intersection Predicates...

Using CASE statement to combine predicates and overlays