Real World Example

A marketplace seller dashboard (think Amazon Seller Central or Meesho Supplier Panel) showed "my last 50 orders with items" and took 3 seconds. Statistics showed 151 statements per request. Here is the before and after for ShopEase:

// BEFORE: entity graph walked lazily -> 1 + 50 (items) + 100 (products) statements
@Transactional
public List<OrderView> recentOrders(Long sellerId) {
    return orders.findTop50BySellerIdOrderByCreatedAtDesc(sellerId).stream()
            .map(o -> new OrderView(o.getId(), o.getStatus(),
                    o.getItems().stream().map(i -> i.getProduct().getName()).toList()))
            .toList();
}

// AFTER: one read-only query returning flat DTO rows, then group in memory
public record OrderLine(Long orderId, OrderStatus status, Instant createdAt, String productName, int qty) {}

@Query("""
    select new com.shopease.order.OrderLine(o.id, o.status, o.createdAt, p.name, i.quantity)
      from Order o join o.items i join i.product p
     where o.seller.id = :sellerId and o.createdAt >= :since
     order by o.createdAt desc
    """)
List<OrderLine> findLines(@Param("sellerId") Long sellerId, @Param("since") Instant since);

@Transactional(readOnly = true)
public List<OrderView> recentOrdersFast(Long sellerId) {
    Map<Long, List<OrderLine>> byOrder = orders.findLines(sellerId, Instant.now().minus(30, ChronoUnit.DAYS))
            .stream()
            .collect(Collectors.groupingBy(OrderLine::orderId, LinkedHashMap::new, Collectors.toList()));
    return byOrder.values().stream().limit(50).map(OrderView::fromLines).toList();
}

-- plus the index that makes the filter fast
CREATE INDEX idx_orders_seller_created ON orders (seller_id, created_at DESC);