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 50% off on Third Edition of PostGIS In Action enter mlobe3 in the Promotional Code box when you check out. Offer ends November 25th, 2021

pgRouting: A Practical Guide is now available in Hardcopy from Hardcopy from Amazon. E-Book or Bulk discounts on Hard-copy from Locate Press.

PostGIS in Action,3rd edition MEAP    PostgreSQL: Up and Running       D3.js in Action 2nd Edition   PostGIS Cookbook 2nd edition