A query plan is the tree of operations a database chooses to execute a query: which tables it scans or reads through an index, in which order and by which method it joins them, and where it sorts and aggregates, each step with an estimated row count and cost. EXPLAIN shows the plan; in PostgreSQL, EXPLAIN ANALYZE also runs the query and shows actual rows and time per step (PostgreSQL, Using EXPLAIN). SQLite’s EXPLAIN QUERY PLAN shows no row counts: read it for SCAN (a full pass) against SEARCH ... USING INDEX, and for USE TEMP B-TREE FOR ORDER BY (a sort the index did not avoid).
In an FDE interview
When a customer says a report or an endpoint is slow, often only in production, read the plan before proposing a fix. Find the step that touches the most rows and compare estimated with actual rows. A large mismatch means the planner misjudged the data: stale statistics, which ANALYZE refreshes, or correlated filters and a function or cast on the filtered column, which a plain ANALYZE does not fix (PostgreSQL, extended statistics cover correlated columns). Then propose a composite index with the equality-filter columns first, then the sort column or one range column (a range before the sort column stops the index from returning rows in order), and state what it costs on every write.
“Slow only in production” points at what differs: data volume, statistics or parameter values, so compare the two plans. EXPLAIN ANALYZE executes the statement, so analyze a write inside BEGIN and ROLLBACK. On a customer’s primary, find the query in pg_stat_statements and get its plan from auto_explain or a replica rather than running a slow statement again, and use EXPLAIN (ANALYZE, BUFFERS) to see reads as well as time; from PostgreSQL 18, ANALYZE includes buffer counts by default (PostgreSQL, EXPLAIN). A prepared statement that turns slow after several executions may have switched to a generic plan (PostgreSQL, PREPARE).
The slow-query question is answered from the plan, not from timing: say which node the time goes to, and what change would turn it into an index search.