Generated SQL, EXPLAIN, and indexes

1. Generated SQL: the source of truth

The SQL log has already given us the shape of the problem: root query, secondary selects, wide SELECT, sudden writes in a read case. The statistics next to it answered another question — how large and repeatable all of this is. But there is another situation as well: there are only a few queries now, secondary selects are not firing, flush is not getting in the way, yet the scenario is still heavy. That means the bottleneck is not in chatty ORM behavior as such, but in the specific SQL that the database actually executes.

This is exactly where generated SQL is needed. We are no longer arguing at the level of “my JPQL looks simple.” We need the SQL that was sent to PostgreSQL, and then EXPLAIN to understand how the database plans to execute it.

When we write JPQL/HQL, the mind starts living in a pleasant reality: “I selected Product, filtered by status, sorted by name — so the database will have a fast query.” The problem is that Hibernate is not a telepath; it is a compiler. It turns your abstraction into concrete SQL, taking into account mapping, column types, the PostgreSQL dialect, fetching settings, and even details such as distinct and join order. That is why, for diagnostics, what matters is not the “idea” but the “execution.”

In a Hibernate-heavy project like our Commerce Persistence Lab, arguing about performance at the JPQL level is roughly like arguing about delivery speed by saying, “well, the courier is on a bicycle.” The bicycle is JPQL, while the real route with traffic jams, traffic lights, and closed streets is generated SQL.

To ground ourselves, let us take a familiar example of a “list of active products” (catalog):

import jakarta.persistence.EntityManager;
import java.util.List;

public class CatalogQuery {
    private final EntityManager entityManager;

    public CatalogQuery(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public List<?> findActive() {
        // JPQL is the "idea of a query", not a guarantee of which SQL will actually go to the database
        return entityManager.createQuery("""
                select p
                from Product p
                where p.status = :status
                order by p.name
                """)
            // Parameters matter: the final plan may depend on the value and its type
            .setParameter("status", ProductStatus.ACTIVE)
            .getResultList();
    }
}

At the JPQL level everything looks “innocent.” But at the SQL level there may be options that you would never guess just by looking at JPQL: which columns were selected exactly (especially in entity loading), which aliases and joins appeared, how the conditions are expressed, which types are compared, whether a left join was added somewhere because of the fetch plan, and so on.

So today’s rule is boring, but it saves nerves: we analyze not JPQL, but the SQL that went to the database. And then EXPLAIN shows how PostgreSQL plans to execute that SQL.

2. How to catch the right SQL

EXPLAIN is powerful, but it has one unpleasant feature: it does not know how to “explain your service method.” It needs a concrete SQL statement. That means our job is to carefully extract exactly the query from the SQL log that corresponds to one use case. And here it is easy to make two classic mistakes: either take the wrong query (because there are hundreds in the log), or take SQL with ? instead of parameters (and then try to explain to the database what status = ? means without a value).

The simplest way to make the SQL log friendly is to mark key queries with comments. Hibernate can add a comment to a query, and later you can find the block in the log literally by text. In our project, this is especially useful on large datasets and in long scenarios where there are many queries.

Here is a minimal example using the Hibernate API (through Session) that you can keep right in the query service:

import jakarta.persistence.EntityManager;
import org.hibernate.Session;
import java.util.List;

public class CatalogQueryService {
    private final EntityManager entityManager;

    public CatalogQueryService(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public List<?> findActiveProducts() {
        // Get the Hibernate Session to attach an SQL comment to a specific query
        Session session = entityManager.unwrap(Session.class);

        return session.createQuery("""
                select p from Product p
                where p.status = :status
                order by p.name
                """)
            // The comment will appear in the SQL log and let us quickly find the needed query among hundreds of lines
            .setComment("catalog-active-products")
            // In SQL the parameter will be '?', while the value will appear as a separate line in the bind logs
            .setParameter("status", ProductStatus.ACTIVE)
            .list();
    }
}

If SQL comments are enabled in the sql-trace profile, the log starts to look something like this, simplified:

/* catalog-active-products */
-- This is generated SQL from the log; parameters here will be placeholders '?'
select p1_0.id, p1_0.sku, p1_0.name, p1_0.status, p1_0.price_amount, p1_0.price_currency
from product p1_0
where p1_0.status=?
order by p1_0.name

And next to it, with bind logging enabled, you will see parameter values, for example:

binding parameter [1] as [VARCHAR] - [ACTIVE]

And here comes the practical point: for EXPLAIN, we need the SQL version with the value substituted (and usually with correct quotes/types). That is, we need to build the “final” SQL roughly like this:

-- It makes sense to run EXPLAIN on SQL that is as close as possible to the real query (including parameter values)
explain
select p1_0.id, p1_0.sku, p1_0.name, p1_0.status, p1_0.price_amount, p1_0.price_currency
from product p1_0
where p1_0.status='ACTIVE'
order by p1_0.name;

A few words about “scenario boundaries.” Even if you found one query, do not rush to run EXPLAIN and celebrate: make sure this is really the dominant query of the scenario, not, for example, a one-off query to the categories table. If you have N+1, then the problem is not a single SQL statement but a series of repeating patterns. But if the statistics say queryExecutionCount=1 and the time is still bad, that is exactly the ideal situation for EXPLAIN: one query that “moves heavily.”

3. EXPLAIN in PostgreSQL: reading the plan

EXPLAIN is a way to ask PostgreSQL: “How are you going to execute this SQL?” The answer will be an execution plan: a tree of steps such as “read the table,” “apply a filter,” “join with another table,” “sort,” “return the result.” It is important to understand the philosophy: the database is not obligated to execute the query “the way it seems logical to you”; it chooses the path it considers cheapest according to its cost model.

Usually the fear starts with the fact that EXPLAIN looks like an incantation in an ancient language:

Sort  (cost=123.45..124.67 rows=100 width=64)
  Sort Key: p.name
  ->  Seq Scan on product p  (cost=0.00..98.00 rows=1000 width=64)
        Filter: (status = 'ACTIVE'::text)

If you remove the mystique, this reads quite naturally: “I am going to scan the product table in full (Seq Scan), select rows by status, and then sort the result by name.”

To avoid drowning in details, at the course level it is enough to understand a few of the most common plan nodes. Here is a small cheat sheet — not a “complete table of all plans in the world,” but what you will actually see in our labs:

Bitmap Heap Scan

Each row in the plan has cost, rows, and width. You do not need to interpret cost as milliseconds (it is not “120 ms,” but some internal relative units). However, it is useful to keep in mind: if the top of the plan has a huge cost, that is almost always why the query is “expensive” in the optimizer’s opinion.

Another important fork is EXPLAIN versus EXPLAIN ANALYZE. The first one does not execute the query, it only shows the plan. The second one executes the query and shows actual times and the actual number of rows. For initial diagnosis and educational scenarios, EXPLAIN is usually enough. EXPLAIN ANALYZE is more powerful, but also more dangerous: on a large dataset it can really “freeze” your console (and the colleague who was trying to “quickly check one migration” at that moment).

4. EXPLAIN: console and Java

When you first try EXPLAIN, a question appears: “Okay, where exactly do I run it?” You can run it in any SQL console: psql, DBeaver, DataGrip, even a web console — as long as it is the same database where you reproduce the scenario. But sometimes it is convenient to run EXPLAIN directly from Java, especially in the project’s lab utilities, so that you can quickly compare several variants or save the plan next to the logs.

The simplest, “no philosophy” option is to manually copy SQL from the log and run it in the SQL console:

-- Manual run in the console: just take the SQL (with parameters) and ask for the plan
explain
select p.id, p.sku, p.name
from product p
where p.status = 'ACTIVE'
order by p.name;

But we also want a “Java version,” so you can see this is not some separate art form. In JPA/Hibernate you can execute EXPLAIN via a native query. PostgreSQL will return the plan as a set of rows, and you can simply print them.

A minimal example:

import jakarta.persistence.EntityManager;
import java.util.List;

public class ExplainRunner {
    private final EntityManager entityManager;

    public ExplainRunner(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public void explain() {
        // EXPLAIN is just SQL, so we execute it as a native query
        List<String> plan = entityManager.createNativeQuery("""
                explain
                select p.id, p.sku, p.name
                from product p
                where p.status = 'ACTIVE'
                order by p.name
                """).getResultList();

        // PostgreSQL returns the plan as strings; print them for diagnostics
        plan.forEach(System.out::println); // Sort ... / Seq Scan ...
    }
}

If you run this in our project, it is logically useful to keep two nuances in mind. First, EXPLAIN is still SQL to the database. If you run it “inside the application,” you still depend on what data is in the database and which indexes have already been applied by Flyway migrations. Second, a native query and plan strings are not an “API for business logic,” but a diagnostic tool. Do not “build a business feature on EXPLAIN”; that is like not making an endpoint called /please-optimize-me.

Sometimes you will want a variant with EXPLAIN ANALYZE. It may look like this:

-- Important: ANALYZE executes the query for real, so use it consciously
explain analyze
select p.id, p.sku, p.name
from product p
where p.status = 'ACTIVE'
order by p.name;

In the course we can mention it as the “next level of accuracy,” but it should be used carefully: it really executes the query and consumes real time and resources.

5. Indexes according to the execution plan

An index in a database is roughly like a table of contents in a book: instead of turning every page, you find the right place using a “map.” But unlike a book, a database index must be maintained on every write, so an index is not free value; it is a compromise: reading got faster, but writing became a little more complex and slower, and disk space was used as well.

The most common beginner mistake is to think like this: “The query is slow, so it needs an index.” In practice, you first need to understand why it is slow. And EXPLAIN answers exactly that question. If the plan says “Seq Scan on a large table + filter by a column,” that is a good candidate for an index. If the plan says “a lot of time is spent on Sort,” then an index may help if it matches the ORDER BY. And if the problem is that you loaded a giant entity graph where half the fields are not needed anyway, an index will not save you: you will quickly find the rows… and still pull a ton of data.

To connect plans and indexes more like an engineer, it is convenient to keep this “symptom translator” in mind:

Filter

Seq Scan

Index Scan

You should be especially careful with columns that have a small number of possible values (low cardinality), for example status with values ACTIVE/INACTIVE/DELETED. An index on such a column may be useful, but it may also not be used if, for example, ACTIVE makes up 95% of the rows. PostgreSQL is not “required” to use an index — it chooses what is cheaper.

And one more important remark specifically in the ORM context: an index does not cure N+1. It may make each individual query a bit faster, but you will still have N queries, only slightly more energetic ones. It is like fixing a leaky boat with a faster pump.

6. Example: active product catalog

To connect everything into one chain, let us take a real read case for our project: “an admin list of active products sorted by name.” At the Java level it usually looks like a simple selection with order by. At the SQL level it turns into a query against the product table. And at the database level the question becomes: how do we quickly find products by status and sort them?

Suppose our query method looks like this: we add both a comment and a read-only hint so that in a read scenario we do not spend extra on dirty checking.

import jakarta.persistence.EntityManager;
import jakarta.persistence.TypedQuery;
import java.util.List;

public class CatalogQueryService {
    private final EntityManager entityManager;

    public CatalogQueryService(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public List<Product> findActiveProducts() {
        // TypedQuery helps keep the contract "we read Product", not raw Object[]
        TypedQuery<Product> q = entityManager.createQuery("""
                select p from Product p
                where p.status = :status
                order by p.name
                """, Product.class);

        return q.setParameter("status", ProductStatus.ACTIVE)
                // Read-only mode: less overhead from dirty checking in a pure read case
                .setHint("org.hibernate.readOnly", true)
                .getResultList();
    }
}

Now enable the sql-trace profile, run the scenario, and pull the generated SQL from the log. It may not look exactly like your JPQL, and that is normal. It will be roughly in this shape:

/* catalog-active-products */
select
    p1_0.id,
    p1_0.sku,
    p1_0.name,
    p1_0.status,
    p1_0.price_amount,
    p1_0.price_currency
from product p1_0
where p1_0.status=?
order by p1_0.name

Then we restore the parameter value (for example, ACTIVE) and build EXPLAIN:

explain
select
    p.id, p.sku, p.name, p.status, p.price_amount, p.price_currency
from product p
where p.status = 'ACTIVE'
order by p.name;

A typical plan without a suitable index (or on a sufficiently large dataset) may look like this:

Sort
  Sort Key: p.name
  -> Seq Scan on product p
       Filter: (status = 'ACTIVE'::text)

What does this picture tell us? The database reads the entire product table, throws away the rows that are not ACTIVE, and then sorts the remainder. If the table is small, that is fine — it may even be faster than an index. But if the table has grown large (our big-dataset profile exists precisely so that it “becomes large”), then Seq Scan + Sort becomes a very real problem.

Now suppose we have an index that supports both filtering and sorting. A classic candidate here is a composite index (status, name). Then the plan may become something like:

Index Scan using idx_product_status_name on product p
  Index Cond: (status = 'ACTIVE'::text)

The idea is that the database can walk the index already in name order (within the status=ACTIVE group) and avoid a separate sort. This is the moment when the conversation about an index becomes meaningful: we see a concrete plan node (Sort + Seq Scan) and understand which part of the work could potentially be removed.

But pay attention: if your SELECT is very wide or pulls heavy fields, for example a huge description text or technical specifications (in real projects this is sometimes tens of kilobytes), then even a perfect index will not save you — you will still physically read and move a lot of data. In that case, the first candidate for improvement is not an index, but a read model (projections) and fetching control, which we already covered in previous modules.

7. Example: order list

The second live scenario from Commerce Persistence Lab is “a list of new orders, newest first.” It is very similar to the catalog case: filter by status and sort by creation date. Only the table is different (purchase_order), and the sort is usually by created_at desc.

At the JPQL level it looks almost the same:

import jakarta.persistence.EntityManager;
import java.util.List;

public class OrderQueryService {
    private final EntityManager entityManager;

    public OrderQueryService(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public List<PurchaseOrder> findNewOrders() {
        // Read case: we care about filtering by status and the order "newest first"
        return entityManager.createQuery("""
                select o from PurchaseOrder o
                where o.status = :status
                order by o.createdAt desc
                """, PurchaseOrder.class)
            // In real SQL this becomes a condition on the status column (and indexes may help here)
            .setParameter("status", OrderStatus.NEW)
            // Read-only mode reduces ORM overhead
            .setHint("org.hibernate.readOnly", true)
            .getResultList();
    }
}

We take the SQL from the log and run EXPLAIN. If there is no index, the database can again easily arrive at Seq Scan + Sort. And that is logical: “read everything, choose what is needed, then sort it.”

If there is an index, for example (status, created_at desc) or at least (status, created_at), the database gets a chance to walk the index in the needed order already. Then the plan may switch to Index Scan and sorting disappears (or becomes much cheaper).

This example is useful precisely as a “thinking template”: sorting is work. When you see Sort in a plan on a large dataset, a concrete question appears: “Can I give the database order through an index?” And yes, sometimes the answer is “no,” because the sort is complex, or because almost all data still matches the filter, or because joins inflate the result. But that is already an engineering conversation, not guessing.

8. Typical mistakes when working with SQL, EXPLAIN, and indexes

Mistake #1: running EXPLAIN on an “approximate SQL” instead of the actual generated SQL.
Sometimes a developer looks at JPQL, mentally translates it into SQL “the way I would write it by hand,” runs EXPLAIN, and draws conclusions. The problem is that Hibernate may have added a join, may have selected different columns, may have used different aliases, and even a different condition shape. In the end you optimize a query that does not exist in reality, while the real generated SQL remains just as expensive.

Mistake #2: forgetting about parameters and types, then being surprised by a strange plan.
status = 'ACTIVE' and status = ACTIVE are two different worlds, and PostgreSQL is quite strict about types. If you substitute a parameter “by eye,” you may get either an error or a plan that does not match the real scenario. The right approach is to take parameter values from the bind logs (sql-trace) and substitute them carefully, with quotes and the correct type.

Mistake #3: treating the index as a cure for the reading shape problem (for example, N+1 or overfetching).
If the scenario has N+1, you can add an index and indeed speed up each individual query. But you will still be executing dozens or hundreds of queries where you really need one proper read case. That is treating symptoms without the cause. First fix the reading shape (fetch plan or projection), and only then decide whether there is any point in indexes for the final SQL shape.

Mistake #4: treating cost as milliseconds and arguing about “130.42 ms” based on EXPLAIN.
cost is PostgreSQL’s internal cost model, not time. It is useful for comparing plans and choosing a strategy, but it does not have to match reality in milliseconds. If you need real time, use EXPLAIN ANALYZE, and do so with the understanding that it measures a specific run on specific hardware.

Mistake #5: blindly running EXPLAIN ANALYZE on a heavy query and then feeling sad.
EXPLAIN ANALYZE actually executes the query. On a large dataset, that may take time, load the database, fill caches, and make life worse for the whole local environment. For educational diagnostics, it is often enough to start with EXPLAIN and only enable ANALYZE when you clearly understand what you are measuring and why.

1

Task

Hibernate deep-dive, level 26, lesson 2

Locked

Query comment and `EXPLAIN` output from the application

Query comment and `EXPLAIN` output from the application

Open

1

Task

Hibernate deep-dive, level 26, lesson 2

Locked

Index via Flyway and reading it from pg_indexes

Index via Flyway and reading it from pg_indexes

Open