PostGIS in Action (3rd)
Where the adventure begins

PostGIS in Action: (ed. #1) Chapter 9 - Performance Tuning

PostGIS In Action 3rd Edition Chapters    PostGIS In Action 2nd Edition Chapters    1st Edition Chapters
Download chapter code and data

  • Planner basics
  • Reading plans
  • Common query patterns
  • Geometry processing for better performance
  • Influencing Plans
  • Indexes and Keys

A core of most relational database engines is the planner. The planner accepts an SQL statement and converts it into data retrieval steps. Most relational data bases you will find have diagnostics for reading these plans. Understanding how to read these and recognizing when something is wrong, is a fundamental skill for achieving fast query speeds.

Two keys of good performance are putting in the right indexes and also writing good SQL. We cover both in this chapter and also cover some of the more esoteric features of PostgreSQL index options.

Although SQL is declarative and fairly easy to read and understand what it is doing, you have many ways of stating the same request in SQL. How you write your SQL statements can impact performance significantly. We demonstrate some of the more common query constructs and how they impact performance as well as various ways of writing the same request.

We also demonstrate some common newbie practices that result in slow performance and demonstrate how to rewrite these to be more efficient.

We also describe various switches you can flip in PostgreSQL to force the planner to use a different plan strategy.

Although this chapter demonstrates spatial SQL and managing spatial data, the concepts are equally applicable to any SQL you write or any data you manage.

Save 40% off on Third Edition of PostGIS In Action and other Manning published books Offer ends August 19th, 2022
PostGIS in Action,3rd edition pgRouting: a Practical Guide SQL In a Nutshell, 4th Edition