EXPLAIN ANALYZE in PostgreSQL: finding slow queries step by step
Published on 2026-10-26 by frfrand
Execution plan and PostgreSQL indexes to optimize APIs
A list of reservations takes three seconds and someone proposes doubling the VPS. Before paying for more CPU, check how PostgreSQL executes that query. EXPLAIN ANALYZE shows you the actual plan, times, and rows processed.
The idea is to solve a data or deployment problem that appears in real applications. We will see the concept without fluff, an implementation example, and what to check before taking it to production.
What EXPLAIN ANALYZE does in PostgreSQL
EXPLAIN estimates a plan without executing the query; with ANALYZE it executes it and records real metrics. In SELECT it is usually appropriate for analysis, but with operations that modify data it can produce changes: never run it cheerfully on production write queries.
It is not enough to repeat a recipe if you do not understand who reads the data, from what process, and with what permissions. In an application with reservations, invoices, or users, a race condition or a poorly made access policy can be more serious than a slow API.
A concrete example
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, fecha, estado
FROM reservas
WHERE cliente_id = 42
ORDER BY fecha DESC
LIMIT 20;
-- Comparar estimaciones y ejecución real
EXPLAIN SELECT * FROM reservas WHERE cliente_id = 42;The names, tables, and keys in this example are illustrative. Try it first in a test base or environment and adapt permissions, indexes, and versions. When there are write operations, do not run sample queries on customer data without reviewing what they modify.
Step by step
- Reproduce the slowness with data similar to production and a representative SELECT.
- Observe total duration, estimated rows, and actual rows.
- Identify large sequential scans, costly sorts, and repeated accesses.
- Evaluate an index compatible with filters and ordering, but check its effect on writes.
- Measure the query again and the API p95, not just the estimated plan cost.
In the end, don't settle for "the SQL didn't error." Verify the real effect from the API and with concurrency or permission tests as appropriate. In databases, there are errors that only appear when two users do something at the same time.
Example work project
A booking platform looks for the latest orders for each customer. A composite index on (cliente_id, fecha DESC) can improve the query if it matches access patterns and data distribution.
That scenario helps distinguish performance from consistency. Speeding up a query is useless if it allows creating two reservations for a single seat, nor does a huge database fix the backend allowing querying another customer's data.
The typical mistake
Creating indexes for every column is not free; they take up space and penalize some writes. ANALYZE runs queries: do not use DELETE/UPDATE examples in production without a transaction and explicit controls.
It's also a good idea to have a recovery plan. Before touching schema, users, or policies, save a verifiable copy or reversible migration and check that the application can keep working during the change.
How would I solve it with ArduMaker?
In PostgreSQL hosted on ArduMaker you can review slow queries and VPS resources. If the bottleneck is an inefficient SQL plan, increasing CPU doesn't necessarily help.
ArduMaker allows you to deploy services from Git and work with databases and processes within the VPS. The panel does not replace your application's logic: transactions, permissions, and validations must be correctly designed in the code and the database.
Three checks before publishing
Integrity: if a request is repeated or fails halfway through, do the data end up in a valid state?
Security: can an authenticated user access only their information, and does the API use a base identity with minimal permissions?
Recovery: do you know how to restore data and revert the compatible code version if an update goes wrong?
If you can answer those three questions with tests, your backend is much better prepared than when you only checked if the endpoint returned 200.
Resources to learn more
To wrap up: choose the simplest solution that allows keeping data correct, secure, and recoverable. Performance comes after measuring.