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.

15.1. The query planner

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.

15.2. Planner statistics

15.3. Using explain to diagnose problems

15.4. Planner and indexes

15.5. Common SQL patterns and how they affect plans

15.6. System and function settings

15.7. Optimizing spatial data

15.8. Summary