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.
pgRouting: A Practical Guide is available in Hardcopy from Hardcopy from Amazon. E-Book or Bulk discounts on Hard-copy from Locate Press.