15 Query performance tuning

 

This chapter covers

  • 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 configurations to yield better performance

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.

Much of what you’ll learn in this chapter 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, and 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.

15.1 The query planner

15.1.1 Different kinds of spatial queries

15.1.2 Common table expressions and how they affect plans

15.2 Planner statistics

15.3 Using explain to diagnose problems

15.3.1 Text explain vs. pgAdmin graphical explain

15.3.2 The plan with no index

15.4 Planner and indexes

15.4.1 The plan with a spatial index

15.4.2 Indexes

15.5 Common SQL patterns and how they affect plans

15.5.1 Subqueries in SELECT

15.5.3 Window functions and self joins