Large multi-part geometries can have massive poorly-fitting bounding boxes that cover large areas of the ocean. Paul shows you how to make bounding boxes more efficient by decomposing with ST_Dump and subdividing with ST_Subdivide.

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

Demonstrates how to optimize PostGIS spatial queries by improving bounding box efficiency through geometry decomposition and subdivision. Using Natural Earth data and 1 million random points, the article shows that decomposing multi-part geometries with ST_Dump reduces query time from 9 to 3.8 seconds, while further subdividing with ST_Subdivide achieves 1.8 seconds—a 5x performance improvement. The techniques address how large, geographically dispersed polygons create inefficient bounding boxes that reduce spatial index effectiveness.

PostGIS Performance: Improve Bounding Boxes...