EXPLAIN: beyond the basics

EXPLAIN is a deep topic, and to do a good introduction talk, you have to skip over a lot of the tricky bits. As such, this talk will not be a good introduction, but instead a deeper dive into some of the things most don’t cover.

The talk starts with some of the less intuitive calculations needed to work out the relationships between operations, rows, threads, loops, timings, buffers, CTEs and subplans. Most popular tools handle at least several of these well, but there are cases where they don’t that are worth being conscious of and alert to.

Next we look at a few rarer issues that can be spotted via EXPLAIN. This includes things like spotting when the query is JIT, planning, or trigger time dominated, spotting the signs of table and index bloat, issues like lossy bitmap scans, and index-only scans fetching from the heap.