Welcome to Advanced SQL Query Optimization. This course is built for backend developers who are tired of guessing why queries are slow and want a systematic approach to diagnosing and fixing database performance issues.

What You'll Learn

5 chapters, sequential progression, all hands-on:

1. Foundations — How databases execute queries, reading EXPLAIN ANALYZE, understanding the cost model

2. Indexing Deep Dive — B-tree internals, composite index column ordering, covering and partial indexes

3. Query Refactoring — Subquery vs. JOIN tradeoffs, CTEs, window functions

4. Spring Boot / JPA Tuning — N+1 detection and fixes, DTO projections, batch operations

5. Production Monitoring — pgstatstatements, HikariCP tuning, repeatable optimization workflow

What You Need

• PostgreSQL 12+ (Docker works: docker run -p 5432:5432 -e POSTGRES_PASSWORD=dev postgres:16)

• A Spring Boot project with Spring Data JPA

• A test table with 100K+ rows

Community

Use the Community Chat to ask questions, share execution plans, and post before/after results. Questions are reviewed daily.

Let's get to work.

Most Spring Boot applications I audit have the same 3 SQL performance problems. They're invisible in development, catastrophic in production, and fixable in an afternoon.

Problem 1: Every @ManyToOne Is Fetching Eagerly

JPA's default fetch strategy for @ManyToOne is EAGER. This means every time you load an entity, it also loads every associated entity — whether you need it or not.

@Entity
public class Order {
    @ManyToOne  // default: FetchType.EAGER
    private Customer customer;
}
// Loading 100 orders = 1 query for orders + 100 for customers = 101 queries when you needed 1.

The fix: Set every @ManyToOne to FetchType.LAZY, then use JOIN FETCH in the specific queries that need the association.

Problem 2: Composite Index Column Order Is Wrong

-- Your query:
SELECT * FROM orders WHERE customer_id = 42 AND status = 'SHIPPED' ORDER BY created_at DESC;

-- Wrong index:
CREATE INDEX idx_orders ON orders(created_at, customer_id, status);

-- Correct index (equality columns first, range/sort last):
CREATE INDEX idx_orders ON orders(customer_id, status, created_at DESC);

The left-prefix rule means column order determines which queries an index can serve. Equality predicates go first. Always.

Problem 3: You're Loading Entire Entities for Read-Only Responses

// Fix: Use a DTO projection
public record OrderSummary(Long id, String customerName, BigDecimal total) {}

@Query("SELECT new com.example.OrderSummary(o.id, c.name, o.total) FROM Order o JOIN o.customer c")
List<OrderSummary> findOrderSummaries();
// 5-6x less memory, 2-3x faster query, zero dirty-checking overhead

These three changes alone typically cut average endpoint response times by 50-80%.

If you want to go deeper — execution plan analysis, partial indexes, window functions, connection pool tuning, and a production monitoring workflow — that's exactly what the full course covers.