How to Read a Query Plan and Fix a Slow Query From Java
Find and fix a slow SQL query behind a Java service: capture the statement, run EXPLAIN ANALYZE, read scans and joins, then add an index or rewrite it.

A slow query from a Java service is fixed in the database, not in Java. The Java side supplies two things, the exact statement and its parameters, and then the work is reading a plan, which is the database's own account of how it will run the query. Plans look intimidating the first time and follow a small set of ideas. This post walks through the steps with PostgreSQL, notes the MySQL differences, and covers the typical causes and fixes: a missing index, an index that cannot be used, stale statistics and sorts that spill to disk. It ends with how to confirm a fix.
1. Get the real statement
Do not reconstruct the query from the repository method. Capture what was sent. Turn on SQL logging for the request as described in debugging Hibernate SQL, and copy the statement with the bound values, since the plan depends on the values. A query that is fast for one customer and slow for another has a different plan for each because of how many rows match. Note the parameter types too. A JDBC setString against an integer column makes the database cast every row, which stops an index from being used; the plan shows it as a filter on a cast column.
2. Ask for the plan
explain (analyze, buffers)
select o.id, o.total
from orders o
where o.customer_id = 4711 and o.status = 'OPEN'
order by o.created_at desc
limit 20;explain alone shows the estimated plan. analyze runs the query and adds the actual time and row counts, which is what you want, with the warning that it executes the statement, so wrap a write in a transaction you roll back. buffers adds how many pages came from cache and from disk. In MySQL, EXPLAIN ANALYZE exists from version 8.0.18, and plain EXPLAIN shows the access type per table. Run it on a database with realistic data. A plan on an empty development database proves nothing, because the optimiser picks different strategies for ten rows and ten million.
3. Read it from the inside out
A plan is a tree. The innermost, most indented nodes run first and feed their parents. A trimmed example, with illustrative numbers:
Limit (cost=... rows=20) (actual time=412.1..412.2 rows=20 loops=1)
-> Sort (cost=... rows=3000) (actual time=412.1..412.1 rows=20 loops=1)
Sort Key: created_at DESC
Sort Method: top-N heapsort
-> Seq Scan on orders o (cost=... rows=3000) (actual time=0.1..398.7 rows=2870 loops=1)
Filter: ((customer_id = 4711) AND (status = 'OPEN'))
Rows Removed by Filter: 1997130Four things to look for. A sequential scan on a large table, where the database reads everything and throws most of it away: Rows Removed by Filter is two million against 2,870 kept. A large gap between estimated rows and actual rows, which means the statistics are wrong and the optimiser chose a plan for a different situation. A sort that does not fit in memory, shown as Sort Method: external merge Disk. And nodes with a high loops count multiplied by a per-loop time, which is the cost of a nested loop that runs thousands of times. The time that matters is the actual time of each node, and the largest node on the path is the one to fix.
4. Fix the cause
The fix depends on what the plan shows.
| What the plan shows | Likely fix |
|---|---|
| Sequential scan filtering most rows away | An index on the filtered columns |
| Index exists but is not used | A function or cast on the column, a leading wildcard, or a type mismatch |
| Estimated rows far from actual | Run analyze to refresh statistics, or raise the statistics target |
| Sort spilling to disk | An index matching the order, or more working memory for the session |
| Many repeated lookups | Rewrite as a join, or fix N+1 in the application |
For the query above, an index on the equality columns followed by the sort column serves the filter and the order together:
create index orders_customer_status_created
on orders (customer_id, status, created_at desc);The column order matters. Equality columns come first, and the column used for ordering last, so the database can walk the index in order and stop after twenty rows. How indexes work and when they hurt writes is in database indexes explained.
When an index exists but is ignored, look at the predicate. where lower(email) = ? cannot use an index on email; an expression index on lower(email) can. where name like '%smith' with a leading wildcard cannot use an ordinary index. where created_at::date = ? casts the column. Rewrite the predicate to compare the column directly, created_at >= ? and created_at < ?, and the index works.
5. Measure again
Run the same explain (analyze, buffers) after the change and compare the time, the rows removed and the buffers read. Then run the real request and compare end-to-end. A plan that improves in the console and does not improve in the application means the application sends a different statement or different parameter types from the one you tested. Keep the before and after plans in the pull request: they are the proof that the index earns its cost. An index slows every insert and update on the table, so confirm the query matters before keeping it.
Other places the time hides
If the plan is fast and the request is slow, the time is elsewhere: connection waiting, which looks like connection not available, a result set fetched whole instead of in pages, as in fetch size, or locks held by another transaction, whose isolation behaviour is explained in isolation levels. A query that runs once per row, the N+1 shape, is invisible in a single plan and is fixed as in fixing N+1.
A rewrite example
Not every slow query needs an index. This one filters in a subquery that runs once per outer row:
select c.id, c.name
from customer c
where (select count(*) from orders o where o.customer_id = c.id) > 10;The plan shows a nested loop with thousands of loops. Rewritten with a join and an aggregate, the database reads orders once:
select c.id, c.name
from customer c
join (select customer_id from orders group by customer_id having count(*) > 10) busy
on busy.customer_id = c.id;Compare the two with explain (analyze) and keep the faster. The optimiser rewrites some forms itself, so verify and do not assume.
When a plan does not help
Plans differ between engines and versions, and the optimiser's choice can change when the data grows. Treat a plan as a measurement at one moment and check the important queries again after large data changes. The PostgreSQL manual explains every node type in using EXPLAIN, and the MySQL side is in the EXPLAIN reference.


