Top 5 Query optimisation Interview Questions
Browse 5 real-world technical and behavioral interview questions about Query optimisation. Review scenarios, edge cases, and architectural best practices.
A table has forty million rows, thirty-five million of them soft-deleted, and every query for active rows has got slower. Would a partial index help, and what would you have to be careful about?
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.
Before you tune anything, how do you decide which queries are worth your time?
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.
Walk me through how you read a PostgreSQL execution plan.
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.
The same statement takes 20ms in psql and four seconds from the application. Where do you look?
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.
A query has an index on the filtered column but the plan shows a sequential scan. Walk me through how you would diagnose it.
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.