How to Get PostgreSQL to Choose the Right Plan

Target Audience: Beginner – Intermediate, who is familiar with the basics of PostgreSQL PostgreSQL's planner decides how to run a query: which join to use, which index, in what order to read the tables. Most of the time its estimates are good enough. But every now and then you meet a query that runs slow even though you expect it to fly. You open EXPLAIN (ANALYZE) and see a nested loop where a hash join clearly belongs, or a sequential scan where an index scan is obvious. So what now? How do you fix this mess? Every such query is a snowflake — each one needs its own approach. Still, there are recommendations you can lean on to optimize your query plans. I'll walk through a few real cases and show why the planner picked that particular plan, and how to help it not get it wrong next time. In the talk we start from the basics — comparing the planner's estimate against the real numbers from EXPLAIN (ANALYZE), because it's precisely the gap between "rows=..." and "actual rows=..." that explains most performance surprises. Then we go through the typical reasons for bad estimates: - stale statistics; - column correlation that ordinary stats fail to capture; - a cost model that doesn't match reality. And then we move on to the more intricate stuff — but I'll save that for the talk, otherwise there's no fun. For each case we first cover a bit of theory, then turn it into practical takeaways. We'll also go over common mistakes and how to avoid them, and what to enable in advance so you spot a bad plan faster. By the end you'll have a small arsenal for hunting down the villains: a checklist that will save you hours next time.