ON THIS PAGE (7 SECTIONS)
- ✓PostgreSQL EXPLAIN shows the plan the database intends to use. EXPLAIN ANALYZE runs it and reports actual time.
- ✓A sequential scan can be the right plan for a tiny table and the wrong plan once the table grows.
- ✓An index helps a selective filter or a sort the query actually uses. An index on every column helps writes get slower.
- ✓Fix the access path before you buy a larger instance for a query that reads the whole table.
A page that listed fifty orders was fine in staging and slow in production. Staging had a thousand rows. Production has millions. The query filters on customer and sorts by created time. The plan reads the whole table and sorts it. More CPU makes that scan faster for a week. The table keeps growing.
PostgreSQL’s EXPLAIN command shows the plan: which scan, which index, which join order, and the estimated cost. EXPLAIN ANALYZE executes the statement and adds actual time and row counts. When the estimate and the actual rows disagree by a lot, the statistics are stale or the predicate is not the one you think. Look there before you rewrite the application around a guess.
A short order of questions
- What is the statement, including the bind values’ shape? A query that filters on a function of a column often cannot use a plain index on that column.
- Is the plan a sequential scan, an index scan, or a sort of a large intermediate result?
- Is the filter selective? An index that matches almost every row may be ignored, correctly.
- Does the ORDER BY match an index you could use, or does the database sort after the scan?
- Are you paging with a large offset? That is an access-path problem too, discussed in Page an API With a Cursor.
- Only then ask whether the instance is out of memory or I/O. Hardware helps a plan that is already reasonable.
Illustrative scenario
This scenario is illustrative. Orders are filtered by customer_id and ordered by created_at, id. EXPLAIN shows a sequential scan and a sort. An index on (customer_id, created_at, id) lets the planner walk that customer’s rows in order. The same statement returns in the time of the fifty rows, not the time of the table. A second query that totals every order for a month still scans the month’s rows, because that is the work. An index does not make a report of every row free. The report may belong in a job, as in Move Slow Work Out of the HTTP Request.
Checklist
- Capture the plan on a copy of production volume, not only on a laptop.
- Compare estimated rows with actual rows.
- Add an index that matches the filter and the order you need.
- Drop indexes nothing uses. They slow writes.
- Re-check the plan after the index. The planner is allowed to ignore it.
- Put a limit on interactive queries. A report can use a different path.
Questions that usually come next
Should every foreign key have an index?
Index the foreign keys you filter or join on. A key that is only stored, and never looked up, still costs writes. Look at the queries you run, not at a blanket rule.
Will an ORM hide this?
An ORM can emit a query that filters on a computed value or loads a relation in a loop. The database still has a plan. Log the SQL and read the plan. The framework is not the access path.
Direct answers
Should every foreign key be indexed?
Index the ones you filter or join on. An unused index still has to be maintained on every write.
Will an ORM choose a good plan for us?
It chooses SQL. The database chooses the plan. Log that SQL and read EXPLAIN on realistic data.
References & Prior Art
PostgreSQL: Using EXPLAIN
PostgreSQL Documentation • PostgreSQL Global Development Group (2026)
Panneer
Systems EngineerPanneer builds production AI data channels, Model Context Protocol (MCP) server boundaries, and evaluation harnesses with deterministic gates.
