Careeroza

Indexes and Query Plans

Hibernate Cannot Fix a Missing Index

If where category_id = ? scans 2 million product rows, no ORM setting will save you. Add indexes for columns used in WHERE, JOIN and ORDER BY. Foreign keys especially: MySQL InnoDB indexes them automatically, PostgreSQL does not.

@Entity
@Table(name = "products",
       indexes = {
           @Index(name = "idx_products_category", columnList = "category_id"),
           @Index(name = "idx_products_category_price", columnList = "category_id, price")
       },
       uniqueConstraints = @UniqueConstraint(name = "uk_products_sku", columnNames = "sku"))
public class Product { ... }

Note: These annotations only matter when Hibernate generates the schema. In production create indexes in a migration script (next module).

Read the Plan

EXPLAIN in PostgreSQL

Copy the SQL from the log and ask the database how it runs it:

EXPLAIN ANALYZE
select p1_0.id, p1_0.name, p1_0.price
  from products p1_0
 where p1_0.category_id = 2
 order by p1_0.price
 limit 20;

Example output

-- before the index
Limit  (actual time=410.2..410.3 rows=20)
  ->  Sort  (Sort Key: price)
        ->  Seq Scan on products p1_0  (rows=48210)   Filter: (category_id = 2)  Rows Removed: 1951790
Execution Time: 412.7 ms

-- after CREATE INDEX idx_products_category_price ON products(category_id, price)
Limit  (actual time=0.04..0.09 rows=20)
  ->  Index Scan using idx_products_category_price on products p1_0
Execution Time: 0.12 ms

Tuning Checklist

  1. Turn on statistics; find requests with many statements (N+1) or slow queries.
  2. Fix N+1 with JOIN FETCH / @EntityGraph / default_batch_fetch_size.
  3. Use DTO projections and readOnly transactions for reads.
  4. Paginate every list endpoint.
  5. Enable JDBC batching and use SEQUENCE ids for bulk writes; flush/clear in chunks.
  6. Add indexes for filter, join and sort columns; check EXPLAIN.
  7. Size the connection pool sensibly (HikariCP; see the Spring Boot material).
  8. Only then consider the second-level cache.