Loading...
Loading...
Browse 5 real-world technical and behavioral interview questions about Query optimisation. Review scenarios, edge cases, and architectural best practices.
A partial index only contains rows matching its predicate, so indexing WHERE deleted_at IS NULL stores five million entries instead of forty million - smaller, shallower, and far likelier to stay cached. The catch is the planner only uses it when it can prove the query predicate implies the index predicate, and it does nothing about dead rows still in the heap.
Rank by the total time a statement shape consumes across a measured window rather than by the slowest single execution, then check what that time was spent waiting on before touching the SQL. The cheap statement running ten thousand times a minute usually outranks the ten-second report.
Read the plan tree innermost-node-first, remember that EXPLAIN only estimates while EXPLAIN ANALYZE executes, multiply each node's actual rows by its loops, and treat a large gap between estimated and actual rows as the primary finding.
When the same query is fast in psql but slow from the application, first separate database time from connection wait, network transfer and ORM hydration. Then compare the real application plan, bind parameters, session settings, fetch size and result volume.
Work outward from the plan: confirm the index is actually usable for the predicate as written, then check whether the optimiser is choosing to ignore it because of selectivity, stale statistics, or a type or collation mismatch that makes the predicate non-sargable.