This chapter covers:
- Planner basics
- Index 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, parallelization, and even storage all affect 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
- How to set postgresql configs to yield better 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.
PostgreSQL has a lot of configuration knobs you can turn to optimize your system. The number of knobs often increases with each major version of PostgreSQL. To keep up to date, a useful site to check out is https://postgresqlco.nf/ which covers all the configuration options available in PostgreSQL, which versions they are available in, and how they’ve changed with each new major version.