A missing or unsuitable database index can make a query much slower, but the title’s 40-millisecond-to-12-second change is a scenario, not a verified incident: no query, database, or execution plan is identified. To find out whether an index is the cause in your case, inspect the query plan and its row estimates before changing indexes or hardware. PostgreSQL provides a documented example of how to do that; it is not established as the database behind the title’s scenario.
What a missing index can—and cannot—explain
An index can let a database locate matching rows without reading an entire table. If a query filters or joins on columns that lack a useful index, the database may need to scan many rows, adding work and increasing execution time.
That does not make every sequential scan a mistake. For a small table, or a query that needs a large share of its rows, reading the table directly may be cheaper than consulting an index and then fetching rows. PostgreSQL’s documentation explains that the planner weighs those costs; its plan costs are estimates in arbitrary units, not elapsed milliseconds. The database and plans behind the title’s 40ms and 12-second figures are not specified, so those numbers should not be treated as independently verified measurements or a benchmark.
How to check a slow query in PostgreSQL
Start with the exact SQL and representative data and parameter values. PostgreSQL’s documentation recommends updating statistics before investigating plan choices: statistics help the planner estimate how many rows a condition will return.
Recommended Free Tools
#1 Best Overall
- Refresh statistics: run
ANALYZEfor the relevant table or tables. This updates planner statistics; it does not create an index. - Inspect the estimated plan: run
EXPLAINfollowed by the query. It shows the plan the planner expects to use without executing the query. - Measure actual execution when needed: run
EXPLAIN ANALYZEfollowed by the query. It executes the query and reports actual row counts and timing alongside estimates. Use caution with queries that modify data, because this command executes them. - Include buffer information: use
EXPLAIN (ANALYZE, BUFFERS)when you need to examine buffer activity as well as execution and row counts.
A plan is a tree: read its scan and other lower-level nodes first, then follow how their output feeds joins, sorts, or aggregates above them. Compare estimated rows with actual rows at relevant nodes. A large mismatch can point to inaccurate estimates; a broad scan, repeated loops, filters discarding many rows, or substantial buffer activity can help locate where work is happening. These are clues to investigate, not proof by themselves that an index is missing.
Check whether an index fits the query
Review the query’s WHERE and JOIN conditions against the proposed index. The indexed columns, their order, data types, and the query’s selectivity all affect whether that index can help and whether the planner considers it worthwhile. A query that returns much of a table may still favor a sequential scan.
Rank #2
PostgreSQL’s documentation cautions that there is no universal recipe for choosing indexes: “It is difficult to formulate a general procedure for determining which indexes to create.” Test a candidate against representative data and parameters, then compare plans and timings. Results on a toy-sized table may not predict behavior at production scale.
When the slowdown appeared suddenly
If the query used to be fast, compare conditions before and after the slowdown rather than assuming an index disappeared. Check whether the workload changed, how long the query takes, what waits are occurring, and whether the execution plan changed. Microsoft’s Azure Database for PostgreSQL troubleshooting guidance follows this broader approach and illustrates that issues such as table bloat and maintenance can also be involved.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Use the relevant before-and-after plans to compare estimated and actual rows, scan and join nodes, rows removed by filters, buffer activity, and execution time under comparable conditions. Confirm a plausible cause before making a change; adding an index or increasing hardware is not a substitute for identifying what changed.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Keep plan timing separate from request timing
EXPLAIN ANALYZE reports database execution timing, not the time spent transmitting results over the network to the client. Its measurement overhead can also matter. A client-observed request may therefore take longer—or behave differently—than the plan’s execution time suggests. Compare like with like when investigating a reported latency change.
Quick Recap
Best Value
- Used Book in Good Condition
Rank #4
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
