What they're testing
Whether you have a system for this, or try things until something gets faster.
The short answer~30 seconds
The order I use: read EXPLAIN (ANALYZE, BUFFERS) to see where the time is; check whether the planner's estimates match reality (a large gap means stale statistics); look at whether it reads more rows than it needs; and only then consider an index. After that come the bigger questions: can we fetch less, can we precompute, does this need to run now at all.
The table holds 5,000,000 rows across roughly 55,556 pages. Each cell below is a group of pages.
The long answer
Reading the plan means knowing what to look at. The key number isn't cost but the gap between estimated rows and actual rows — a hundredfold gap means the planner is deciding on bad information, and any index you add afterwards may still go unused. Buffers shows how much data was really read, and it's the quantity that correlates most tightly with elapsed time.
One class of problem that's routinely missed: the query is fast by hand and slow from the application. The usual cause is a prepared statement's generic plan — after five executions Postgres switches to a parameter-independent plan, and on skewed data that generic plan is markedly worse. Check it by EXPLAINing the prepared statement itself rather than the SQL with values substituted.
At the "fetch less" step, the best question is which screen this query serves. Very often it's a SELECT * on a table with a large JSON column while the UI shows three fields; naming the columns alone cuts the bytes tenfold and makes an index-only scan possible. It's the cheapest and most effective class of fix, and it adds no indexes.
What they'll ask next
?When is a materialized view the right call?
When the query is expensive, staleness is acceptable, and it's read far more often than the underlying data changes — aggregate reports being the classic. The price is managing refreshes, and a REFRESH without CONCURRENTLY locks the view while it runs.
These lose points
- Adding an index the moment a query is slow. Sometimes right, but it's the fourth step and not the first.
- Benchmarking on a dev box with a thousand rows. Execution plans change completely with data size.