Query Performance Rewrites: What Works, What Doesn't, and Why ~ Adam Machanic

This is an IN-PERSON meeting

Speaker: Adam Machanic

Abstract:

For many database professionals, crafting an optimal query is half guesswork and half black art. If you switch the order of the WHERE predicates while burning sandalwood incense, will the query suddenly move a bit quicker? Or maybe you need to use a CTE—that will help, right? While speculative methods like these can eventually yield success, it doesn't come without excessive hand wringing and time wasting. But it doesn't have to be this way! In this session we'll start by revisiting the foundations. You’ll learn about the key logical guarantees on which query optimization is based, and why a rewrite that doesn't bend a guarantee is almost never going to help. From there we'll investigate various query patterns and take a hard look at some commonly suggested tuning advice. Are subqueries a problem? Do you need to worry about join order in your query? What about the oft-maligned IN predicate? All of this will be illustrated with various examples in both SQL Server and PostgreSQL. By the end of the session, you'll understand which rewrites might work, in which scenarios, and—most importantly—which you can totally ignore. And as a bonus, you might even be able to retire your incense collection.