Query performance isn’t a concern relegated to engineers at Google’s scale, but making queries performant seems intimidating. First, PostgreSQL’s query planner and EXPLAIN output seem mysterious.
Making matters worse, constructs in descriptive languages like SQL don’t necessarily map directly to their execution. But we shouldn’t let those realities stop us from understanding how our queries will be executed. And computational complexity is a useful tool to address our performance concerns.
In this talk we’ll explore the basics of computational complexity, what operations are available to the query planner, and how the two relate to each other. Next, we’ll discuss avoiding general performance traps in writing PostgreSQL queries. Finally, we’ll evaluate several example queries (ranging from familiar to arcane) to see how understanding the limitations of the planner’s execution primitives can help us make our queries more performant.