Chapter 15. Query performance tuning
This chapter covers
- Planner basics
- Reading plans
- Common query patterns
- Spatial processing for better performance
- Influencing plans
When dealing with several tables at once—especially large ones—tuning queries becomes a major consideration. The way you write your queries is also important. Two queries can return exactly the same data, but one can take a hundred times longer to finish. The complexity of spatial objects, memory allocation, and even storage affects performance.
In this chapter, you’ll learn
- How to use the planner and read query plans
- How to write efficient queries
- How to monitor query performance
- How to organize spatial data to improve query performance
Much of this information has more to do with PostgreSQL proper and SQL in general, but PostGIS piggybacks on PostgreSQL. Learning more about PostgreSQL and how it executes queries will not only make your non-spatial queries faster, but will also improve your spatial queries significantly.
The data and code used in this chapter can be found at www.postgis.us/chapter_15_edition_2. Some examples also use data from prior chapters.
All relational databases employ a query planner to digest the raw SQL statement prior to executing the query. But the planner isn’t perfect, and it can optimize some SQL statements better than others.