The database can lie about a lot of things. It cannot lie about the query plan. EXPLAIN shows the exact sequence of operations the planner has chosen, the estimated row counts at each step, and the cost model that produced the choice. It is the most underused diagnostic tool in the backend developer toolkit, and the reason is that the output looks like a tax form on first contact.
This post is the field guide most developers want when they first try to read a query plan and bounce off. It covers what each operator class actually does, how to read estimated costs, the small set of patterns that account for most slow queries, and the discipline of running EXPLAIN ANALYZE often enough that the query plan becomes a thing you reach for rather than a thing you avoid.
The two flavors that matter
PostgreSQL has EXPLAIN and EXPLAIN ANALYZE. The first shows the planner's predicted plan with estimated costs. The second runs the query and shows the actual time and row counts at each node. The difference between estimate and actual is where most performance problems hide. A planner that thinks a step will return 10 rows but actually returns 100,000 will pick the wrong join strategy, the wrong index, the wrong everything. The estimate-vs-actual divergence is the highest-leverage signal in the entire output.
SQLite has EXPLAIN QUERY PLAN, which is much terser but gives you the operator tree. MySQL has EXPLAIN with a FORMAT=TREE option in 8.0+ that approximates the PostgreSQL shape. The conceptual model is the same across engines: the plan is a tree of operators, each consuming rows from its children and producing rows for its parent.
The operators you will see most
The five most common operators account for nearly every query plan you will read. Seq Scan reads every row of a table sequentially. It is correct for small tables and full-table aggregates, wrong for selective predicates on large tables. Index Scan walks an index to find matching rows, then fetches the rows from the table. Index Only Scan is the same but answers the query entirely from the index without table access — this requires the index to cover all queried columns and the visibility map to be up to date. Bitmap Heap Scan with a Bitmap Index Scan child is the planner's choice when an index is selective enough to use but not selective enough that random I/O wins; it builds a bitmap of matching pages and reads them in physical order. Nested Loop, Hash Join, and Merge Join are the three join algorithms.
The choice between these is mechanical: nested loop is right when the outer side is small and the inner side has an index on the join key, hash join is right when both sides are large and one fits in memory, merge join is right when both sides are already sorted. The planner picks based on row count estimates, which is why bad statistics produce bad plans.
Reading the cost numbers
Each node has two costs: the startup cost (work to produce the first row) and the total cost (work to produce all rows). The numbers are in arbitrary planner units, not milliseconds. They are useful for comparing plans, not for predicting wall-clock time. The rows estimate is more diagnostic — if it is wildly off compared to the actual rows in EXPLAIN ANALYZE output, your statistics are stale or the planner has hit a known correlation it cannot model.
The width column is row size in bytes, which matters mostly for memory-bound operations like hash joins. loops in EXPLAIN ANALYZE output shows how many times a node was executed — a Nested Loop's inner side will show loops=N where N is the outer row count, and the total cost is per-loop times loops.
The five patterns that account for most slow queries
The pattern catalog is small. Sequential scan on a large table with a selective WHERE clause means a missing index. Index scan with high row-removal-by-filter means the index matches the predicate column but the additional WHERE conditions are filtering out most matches; you need a multi-column index in the right order. Hash join with hash batches written to disk means the join exceeded work_mem and is now performing disk-based hashing; raising work_mem for that connection or reducing the join cardinality fixes it. Nested loop with large outer side means the planner thought the outer side was small but it was large; usually fixed by ANALYZE-ing the table or rewriting to encourage a different join. Sort with disk-based sorting means the result set exceeded work_mem; sometimes fixed by an index that provides ordering, sometimes by raising work_mem, sometimes by paginating differently.
The discipline is to run EXPLAIN ANALYZE on slow queries before reaching for any other tool. Most slow queries are one of these five patterns, and the plan tells you which one within ten seconds of looking.
What the plan does not tell you
The plan does not tell you about lock contention, replication lag, connection pool exhaustion, or network latency between your application and the database. A query that runs in 5ms in EXPLAIN ANALYZE can take 500ms in production because of any of those, and you will spend hours staring at a fast plan trying to figure out why production is slow. The plan is necessary, not sufficient. It rules out one class of problems and points to others.
It also does not tell you about cache effects. The first run of a query may show buffer reads dominating cost; the tenth run, with hot pages, shows essentially the same plan but completes in a fraction of the time. EXPLAIN ANALYZE BUFFERS shows the buffer hit/read split, which is the closest the plan output comes to surfacing cache state.
The auto_explain trick
The single highest-leverage configuration change for query-plan diagnostics is enabling auto_explain with a threshold like 200ms. PostgreSQL will log the plan of every query that exceeded the threshold, with the actual times. Turning this on in production for a week and reading the resulting log is the fastest way to find the queries that need attention. Do not leave it on at zero threshold — the logging overhead is real for high-throughput systems.
Where the four products fit
DocuMint, CronPing, FlagBit, and WebhookVault all run on SQLite, where EXPLAIN QUERY PLAN output is terser but the discipline is the same: run it on every query in a hot path, and run it on every new query during code review. The cheapest performance bug is the one you find while writing the query, not the one you find at 3am during an incident.
The skill compounds
Reading query plans is a skill that compounds. The first time you read one it feels like deciphering a hieroglyph. By the tenth time it feels like reading a prose paragraph. By the hundredth time you start writing queries differently because you can predict what the plan will look like. The investment of a few hours becomes a structural advantage in every database conversation you have for the rest of your career.
The fastest way to develop the skill is to take every slow query you encounter for a month and run EXPLAIN ANALYZE on it before doing anything else. Do not read the application code. Do not theorize. Run the plan, identify the pattern, fix the pattern, verify with another plan. The pattern catalog you build by doing this is the one that lets you debug production problems while everyone else is still reading code.