PostgreSQL slow? Read the query plan before adding an index

Let's discuss your project →

When a PostgreSQL query feels slow, the instinct is often to add an index first. That can help, but it should not be the first move. A more reliable workflow is to capture a reproducible case, read the execution plan, check the estimates, and only then decide whether an index is the right change. PostgreSQL’s documentation makes the key distinction clear: EXPLAIN shows the chosen plan, while EXPLAIN ANALYZE actually runs the query and reports observed execution details. (PostgreSQL docs)

Define the case first

Use a concrete scenario, not a vague complaint. For example: a monthly order search takes longer than expected. Before changing anything, save the exact query, its parameters, the size of the test dataset, and the PostgreSQL version. If the query touches sensitive data, use authorized data only and work in a separate environment.

A useful question at this stage is simple: what latency is acceptable for the user, and does the slowdown happen in one request or under concurrent load? A single isolated run does not automatically represent real traffic.

Sample input for a test notebook

  • Query: SELECT id, total FROM orders WHERE customer_id = 42;
  • Parameters: customer_id = 42
  • Dataset: test copy of production-like rows
  • Environment: isolated test database
  • Goal: identify whether the bottleneck is filtering, sorting, or data access pattern

Read the plan before changing the schema

The first diagnostic output to collect is EXPLAIN. PostgreSQL presents the chosen plan as a tree of nodes with estimated costs and row counts. Those estimates are used by the planner for decision-making; they are not a direct measure of wall-clock time. The documentation also notes that example plans can vary because statistics are sampled and costs depend on the platform. (PostgreSQL docs)

A simplified, simulated example:

EXPLAIN
SELECT id, total
FROM orders
WHERE customer_id = 42;

Simulated output:

Seq Scan on orders  cost 0.00..1820.00 rows 120
Filter: customer_id = 42

This does not prove that an index is missing. It only shows that, based on the current statistics and cost model, the planner prefers a sequential scan for this case.

What to look for in the plan:

  • scan type: sequential scan, index scan, bitmap heap scan
  • filters: which conditions are applied where
  • sort steps: whether PostgreSQL must order rows after reading them
  • estimated rows: whether the planner expects a narrow or broad result set

Use EXPLAIN ANALYZE only in a controlled test

If estimates are not enough, move to EXPLAIN ANALYZE. PostgreSQL documents that this command actually executes the query, so it can consume resources and may have side effects if the statement modifies data. That makes it useful for measurement, but not harmless as a preview. (PostgreSQL docs)

A second simulated example:

EXPLAIN
SELECT id, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at;

Simulated output:

Bitmap Heap Scan on orders
Recheck Cond: customer_id = 42
Sort

Here the plan suggests that PostgreSQL first gathers matching rows and then sorts them. In a real investigation, the next question is not simply “add an index?” but “what is the dominant cost: filtering, sorting, or the volume of rows read?”

Decide on an index with operational trade-offs in view

An index can improve read performance and still create costs elsewhere. Before rolling out a new one, review:

  • write overhead on inserts and updates
  • storage space consumed
  • maintenance and rebuild effort
  • the deployment process in the real environment

That broader view matters because a faster query is not automatically a better system change. If the table is updated frequently, the added index may slow writes enough to offset the gain.

A practical five-step workflow

  1. Gather the exact slow query and its context.
  2. Run EXPLAIN and inspect the chosen plan.
  3. Check scans, filters, sorts, and estimated rows.
  4. If needed, run EXPLAIN ANALYZE in test only.
  5. Evaluate any index together with write impact, storage, and concurrency.

What a useful output bundle should contain

A good investigation should end with a small technical record, not a guess:

  • the original plan
  • the measured result in a test environment, if EXPLAIN ANALYZE was used
  • a short decision note explaining why the change was accepted or rejected

Acceptance checks

  • The tested query matches the real case.
  • Only one change was introduced at a time.
  • Functional output stayed the same.
  • The test ran outside production or in a controlled window.
  • The impact on writes and maintenance was discussed before deployment.

What this means for a technical team

For CTOs, backend developers, and data engineers, the value of this method is discipline: it reduces the risk of tuning the wrong layer. PostgreSQL’s plan is not just an internal detail; it is a decision aid for choosing between scan types, sort strategies, and indexes based on evidence, not intuition. (PostgreSQL docs)

If the query is still slow after reading the plan, that is useful information too. It tells you where to look next and what trade-offs to test, instead of adding an index blindly and hoping for the best.

Sources