jOOQ's MULTISET on PostgreSQL: Nested Collections Without the Cartesian Product

Good evening. I have opinions about how you load your collections.

The Java ecosystem has spent twenty years loading nested collections from relational databases, and in that time it has developed two dominant approaches. Both are wrong.

I do not say this lightly. Hibernate's architects are serious people who have thought deeply about object-relational mapping, and the compromises they made were reasonable given the SQL capabilities available in 2004. But it is no longer 2004. PostgreSQL has evolved considerably. The constraints that made those compromises necessary have dissolved. And yet the compromises persist, embedded in annotations and entity mappings across millions of Java applications, producing Cartesian products and unnecessary round trips that the database has long been capable of avoiding.

Hibernate's JOIN FETCH produces a single query — commendable — but that query returns a Cartesian product of every parent row multiplied by every child row. An order with 8 items appears as 8 rows. An order with 8 items and 3 tags per item appears as 24 rows. The application receives 24 copies of the order's columns and deduplicates them in memory. The database did the work of assembling data. Then it did the work of duplicating that data. Then the application did the work of un-duplicating it. Three operations where one would suffice.

Hibernate's @BatchSize avoids the Cartesian product — also commendable — but replaces it with a configurable number of additional queries. Better than one query per row. Still not one query, period.

jOOQ 3.15 introduced a third approach. The jOOQ team has documented it thoroughly in their MULTISET reference — and what follows explains why it matters. MULTISET sends a single query to PostgreSQL with no JOINs on the collection tables. The nested data arrives as JSON arrays inside each row. One query, one round trip, zero duplicated rows, correct LIMIT behavior.

It is, frankly, how this should have worked from the beginning.

What is wrong with JOIN FETCH, precisely?

Nothing, in the simple case. An order with its customer — a many-to-one — is a single JOIN that produces no row duplication. Clean, efficient, correct. The JOIN returns one row per order because the relationship is one-to-one from the order's perspective. No multiplication occurs.

The trouble begins the moment you fetch a collection. A one-to-many relationship inverts the cardinality: an order with 8 items means 8 rows returned from PostgreSQL, each carrying a full copy of the order's columns. Add the customer JOIN and each row also carries a copy of the customer's columns. The data transfer has multiplied by 8, and Hibernate must deduplicate the order objects in memory using its identity map.

// Hibernate's JOIN FETCH — the industry standard approach
// "Give me orders with their items."
String jpql = """
    SELECT o FROM Order o
    JOIN FETCH o.items
    JOIN FETCH o.customer
    WHERE o.status = :status
    """;

List<Order> orders = em.createQuery(jpql, Order.class)
    .setParameter("status", "COMPLETED")
    .getResultList();

// What Hibernate sends to PostgreSQL:
// SELECT o.*, i.*, c.*
// FROM orders o
// INNER JOIN order_items i ON i.order_id = o.id
// INNER JOIN customers c ON c.id = o.customer_id
// WHERE o.status = 'COMPLETED'
//
// 500 orders x 8 items each = 4,000 rows returned.
// Each order's columns duplicated 8 times.
// Add a second collection (tags) and it becomes
// 500 x 8 x 3 = 12,000 rows. Cartesian product.

Now add a second collection — tags on each item, say — and the multiplication compounds. 8 items times 3 tags produces 24 rows per order. For 500 orders, that is 12,000 rows transferred over the wire, carrying 11,500 copies of data that appeared in the first 500. The number of rows grows as the product of collection cardinalities, not the sum. This is the Cartesian product, and it is the defining pathology of JOIN FETCH on nested collections.

Hibernate warns about this. The Hibernate documentation calls it a "Cartesian product problem" and recommends against fetching multiple collections in a single query. The warning is buried in a section most developers reach only after the production alert fires.

The bandwidth cost, quantified

I find it useful to express the Cartesian product not as an abstract concern but as a concrete number. Allow me a brief inventory.

-- The Cartesian product, by the numbers.
-- Orders table: 500 rows, 12 columns, ~200 bytes each.
-- Order items: 8 per order, 6 columns, ~100 bytes each.
-- Item tags: 3 per item, 4 columns, ~60 bytes each.

-- JOIN FETCH with one collection (orders + items):
--   500 orders × 8 items = 4,000 rows
--   Each row: 200 bytes (order) + 100 bytes (item) = 300 bytes
--   Total: 4,000 × 300 = 1,200,000 bytes (1.2 MB)
--   Unique data: 500 × 200 + 4,000 × 100 = 500,000 bytes (500 KB)
--   Waste: 58%

-- JOIN FETCH with two collections (orders + items + tags):
--   500 × 8 × 3 = 12,000 rows
--   Each row: 200 + 100 + 60 = 360 bytes
--   Total: 12,000 × 360 = 4,320,000 bytes (4.3 MB)
--   Unique data: 100K + 400K + 240K = 740,000 bytes (740 KB)
--   Waste: 83%

-- MULTISET:
--   500 rows. JSON arrays inline.
--   Total: ~780,000 bytes (780 KB)
--   Waste: ~5% (JSON syntax overhead)

-- The Cartesian approach transferred 5.5x the actual data.

4.3 megabytes transferred where 780 kilobytes would suffice. On a local development machine with a Unix socket connection to PostgreSQL, this overhead is invisible — the data never touches a network interface. In production, where the application server and database may be separated by a network hop with finite bandwidth and non-zero latency, the difference manifests as time. Time serializing rows in PostgreSQL. Time pushing bytes across the wire. Time deserializing and deduplicating in Hibernate's persistence context. Time that compounds with every request.

I have observed this pattern in production systems where the application server's network interface was saturated not because the data volume was genuinely large, but because 83% of the bytes crossing the wire were duplicated column values that would be discarded on arrival.

The LIMIT problem

And then there is pagination. If you add .setMaxResults(100) to a JOIN FETCH query with a collection, one of two things happens, neither of them desirable.

Option one: Hibernate applies the limit in memory. It fetches all matching rows from PostgreSQL — every order with every item — then discards everything beyond the first 100 orders in the JVM. This is correct in result but catastrophic in resource consumption. Hibernate logs a warning: HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory. The warning is polite. The consequences are not.

Option two: Hibernate passes the LIMIT to PostgreSQL, which applies it to the flattened row count. LIMIT 100 returns 100 rows of the Cartesian product — which might represent 12 complete orders and a partial 13th, depending on how many items each order has. The result is incorrect, and you discover this only when a customer reports missing data.

Either way, the result is not what you asked for. The fundamental issue is that JOIN FETCH conflates two different questions — "which parent entities?" and "what child data?" — into a single flat result set. Pagination operates on the flat result set, not on the parent entities. The abstraction has leaked.

What about @BatchSize?

Hibernate's @BatchSize is the practical workaround that most production Hibernate applications land on eventually. Instead of joining the collection into the main query, Hibernate fires a separate query with an IN clause, batching the foreign key lookups.

// Hibernate @BatchSize — trading Cartesian for N/M+1
@Entity
public class Order {
    @OneToMany(mappedBy = "order")
    @BatchSize(size = 25)
    private List<OrderItem> items;

    @ManyToOne
    private Customer customer;
}

// First query: SELECT * FROM orders WHERE status = 'COMPLETED';
// Then Hibernate fires batched queries:
// SELECT * FROM order_items WHERE order_id IN (?, ?, ?, ... ?); -- 25 at a time
//
// For 500 orders: 1 + ceil(500/25) = 21 queries.
// Better than 501 (pure N+1). Worse than 1.
// And the batch size is global — you pick a number
// and hope it fits every use case. It will not.

For 500 orders with a batch size of 25, that is 21 queries: 1 for orders plus 20 batches for items. Reasonable. Not elegant, but reasonable. The Cartesian product is eliminated. Each row carries only its own data. Pagination works correctly because the parent query is a clean SELECT with a proper LIMIT.

The problems are practical, not architectural:

• The batch size is a global annotation. You pick one number and apply it to the entity. A dashboard listing 500 orders and an API endpoint fetching 5 orders share the same batch size. For the dashboard, a batch size of 25 means 20 extra queries — acceptable. For the API endpoint fetching 5 orders, a batch size of 25 means the IN clause is padded with nulls, which is harmless but inelegant. The problem surfaces at the extremes: a bulk export of 50,000 orders with batch size 25 fires 2,001 queries. One of them is over-batched, the other under-batched, and you cannot tune for both without introducing a second entity mapping or a custom query.
• The query count is O(N/B + 1), not O(1). It scales linearly with the number of parent rows. At 5,000 orders with batch size 25, you send 201 queries. At that volume, the round-trip overhead — the time spent sending the query, waiting for PostgreSQL to parse and plan it, and receiving the result — becomes the bottleneck. Each round trip adds 0.1-0.5ms of network latency, and 201 of them add 20-100ms of pure waiting time on top of the actual execution cost.
• Nested collections compound. Items batched in groups of 25, then tags for those items batched in groups of 25. For 500 orders with 8 items and 3 tags per item, the query count becomes: 1 (orders) + 20 (item batches) + 160 (tag batches, assuming 4,000 items / 25 per batch) = 181 queries. The round-trip overhead alone is 18-90ms before any query actually executes.
• Each batch is an independent query. PostgreSQL plans and optimizes each batch separately. The prepared statement cache helps with repeated shapes, but the planner still performs the work 21 times instead of once. With jOOQ's MULTISET, the planner sees the entire query — all collections, all nesting levels — and optimizes it as a unit.

@BatchSize is a competent compromise. It solved the Cartesian product problem without requiring fundamental changes to how ORMs interact with relational databases. But MULTISET is not a better compromise. It is a different category of solution — one that asks the database to return structured data instead of flat rows, which is what we wanted all along.

And what about EntityGraph?

I should address the objection that will occur to Hibernate 6 practitioners: "We don't use JOIN FETCH directly anymore. We use @NamedEntityGraph."

// Hibernate EntityGraph — the "modern" alternative to JOIN FETCH
@NamedEntityGraph(
    name = "Order.withItemsAndTags",
    attributeNodes = {
        @NamedAttributeNode(value = "items", subgraph = "items.tags"),
        @NamedAttributeNode("customer")
    },
    subgraphs = {
        @NamedSubgraph(name = "items.tags",
            attributeNodes = @NamedAttributeNode("tags"))
    }
)
@Entity
public class Order {
    @OneToMany(mappedBy = "order")
    private List<OrderItem> items;

    @ManyToOne
    private Customer customer;
}

// Usage:
EntityGraph<?> graph = em.getEntityGraph("Order.withItemsAndTags");
List<Order> orders = em.createQuery(
        "SELECT o FROM Order o WHERE o.status = :status", Order.class)
    .setParameter("status", "COMPLETED")
    .setHint("jakarta.persistence.fetchgraph", graph)
    .getResultList();

// EntityGraph tells Hibernate WHAT to load, not HOW.
// Under the hood, it still generates JOIN FETCH.
// Same Cartesian product. Same row duplication.
// Same LIMIT problem. Better API, same SQL.

EntityGraph is a cleaner API for declaring what to load. It separates the fetch strategy from the JPQL query, which is an improvement in code organization. But it does not change the fundamental mechanism. Under the hood, Hibernate still generates JOIN FETCH SQL. The same Cartesian product. The same row duplication. The same LIMIT problem.

EntityGraph tells Hibernate what to load. It does not tell Hibernate how to load it. The "how" remains JOIN FETCH or @BatchSize, and the trade-offs of each remain unchanged. A better API over the same mechanism is a better API, not a better mechanism.

This is not a criticism of the EntityGraph design. It is an observation that the problem was never the API. The problem was that SQL's relational model returns flat rows, and flat rows cannot represent nested collections without either row duplication (JOIN) or multiple queries (batch). MULTISET introduces a third option: nested aggregation within a single query. That required PostgreSQL's json_agg(), which matured to production quality around PostgreSQL 9.4. The mechanism was simply not available when Hibernate's fetch strategies were designed.

How does MULTISET actually work?

jOOQ's multiset() translates to a correlated subquery wrapped in PostgreSQL's json_agg(). The subquery runs once per outer row, aggregates its results into a JSON array, and returns that array as a single column value. The outer query sees each collection as a single text column containing JSON, not as a multiplied set of rows.

// jOOQ MULTISET — nested collections without the JOIN
var result = ctx.select(
        ORDERS.ID,
        ORDERS.TOTAL,
        ORDERS.STATUS,
        ORDERS.CREATED_AT,
        field(select(CUSTOMERS.NAME)
            .from(CUSTOMERS)
            .where(CUSTOMERS.ID.eq(ORDERS.CUSTOMER_ID))
        ).as("customer_name"),
        multiset(
            select(
                ORDER_ITEMS.ID,
                ORDER_ITEMS.QUANTITY,
                ORDER_ITEMS.PRICE,
                ORDER_ITEMS.PRODUCT_NAME
            )
            .from(ORDER_ITEMS)
            .where(ORDER_ITEMS.ORDER_ID.eq(ORDERS.ID))
        ).as("items").convertFrom(r -> r.map(Records.mapping(OrderItemDto::new)))
    )
    .from(ORDERS)
    .where(ORDERS.STATUS.eq("COMPLETED"))
    .fetch();

// One query. One round trip. No Cartesian product.
// Each order carries its items as a nested JSON array,
// not as duplicated flat rows.

The Java API is type-safe — Records.mapping() deserializes the JSON into your DTO at compile-checked field positions. But what matters for performance is the SQL it generates:

-- What jOOQ actually sends to PostgreSQL:
SELECT
  orders.id,
  orders.total,
  orders.status,
  orders.created_at,
  (SELECT customers.name
   FROM customers
   WHERE customers.id = orders.customer_id
  ) AS customer_name,
  (SELECT coalesce(
    json_agg(json_array(
      t.id, t.quantity, t.price, t.product_name
    )),
    json_array()
  )
  FROM (
    SELECT order_items.id, order_items.quantity,
           order_items.price, order_items.product_name
    FROM order_items
    WHERE order_items.order_id = orders.id
  ) AS t
  ) AS items
FROM orders
WHERE orders.status = 'COMPLETED';

-- No JOINs. No Cartesian product. No row duplication.
-- The nested collection is a correlated subquery that
-- aggregates into JSON inside PostgreSQL.

No JOINs on the collection tables. The order_items data lives inside a correlated subquery that produces a JSON array for each order. PostgreSQL returns exactly 500 rows — one per order — each carrying its items as an embedded array.

If you have spent time in the SQL world, a correlated subquery in the SELECT clause may set off alarm bells. The conventional wisdom — "correlated subqueries are slow, rewrite them as JOINs" — has been repeated so often that it functions as received truth. I would like to examine that truth with some care, because the distinction matters here.

A correlated subquery is slow when it triggers a sequential scan on every execution. If the subquery scans 10,000 rows for each of 500 outer rows, that is 5 million row reads — genuinely expensive. But if the subquery hits an index and returns 8 rows in 0.03ms, then 500 executions cost 15ms total. The cost structure is identical to a nested loop join, which the PostgreSQL planner chooses routinely for small inner tables. The "correlated subqueries are slow" heuristic is accurate for unindexed tables and inaccurate for indexed ones. MULTISET depends on the latter.

The implications cascade:

• No Cartesian product. Each order appears once. The item data is aggregated into a JSON array, not cross-joined into flat rows. The row count of the result set equals the row count of the parent query, regardless of how many children each parent has.
• LIMIT works correctly. LIMIT 100 returns 100 orders. Not 100 flattened rows. Not 12 orders after deduplication. 100 orders, each carrying its complete nested data. The LIMIT operates on the parent query, and the correlated subqueries execute only for the rows that survive the LIMIT. This is not merely correct — it is also efficient, because rows beyond the LIMIT never trigger their subqueries at all.
• No in-memory deduplication. Hibernate receives 12,000 flat rows and rebuilds the object graph using its identity map — a hash map lookup for every row to determine whether the parent entity already exists. jOOQ receives 100 rows with JSON arrays and deserializes them. Less data transferred, less memory consumed, less CPU spent on deduplication.
• Nesting is composable. A MULTISET inside a MULTISET produces a nested correlated subquery. The Cartesian explosion that haunts JOIN FETCH simply cannot occur, because each level of nesting is an independent aggregation. Orders contain items as JSON. Items contain tags as JSON. The row count remains: number of orders.

Type safety: where jOOQ earns its complexity

jOOQ is more verbose than Hibernate. This is undeniable. An entity annotated with @OneToMany and @BatchSize requires two lines of configuration. The equivalent jOOQ MULTISET query requires fifteen lines of explicit column selection, subquery composition, and DTO mapping. One might reasonably ask whether the performance improvement justifies the syntactic overhead.

It does, and the reason is not only performance. It is compile-time type safety.

// jOOQ's type-safe mapping — compile-time checked deserialization
// Define the DTO as a Java record:
record OrderItemDto(
    Long id,
    Integer quantity,
    BigDecimal price,
    String productName
) {}

// The multiset() call uses Records.mapping() to bind
// positional columns to the record constructor:
multiset(
    select(
        ORDER_ITEMS.ID,        // Long   → position 1
        ORDER_ITEMS.QUANTITY,  // Integer → position 2
        ORDER_ITEMS.PRICE,     // BigDecimal → position 3
        ORDER_ITEMS.PRODUCT_NAME // String → position 4
    )
    .from(ORDER_ITEMS)
    .where(ORDER_ITEMS.ORDER_ID.eq(ORDERS.ID))
).as("items").convertFrom(r -> r.map(Records.mapping(OrderItemDto::new)))

// If you add a column to the SELECT without updating the DTO,
// the compiler catches it. If you reorder columns, the compiler
// catches it. If you change a type, the compiler catches it.
//
// Hibernate's HQL returns Object[] and casts at runtime.
// jOOQ's MULTISET returns typed records and validates at compile time.
// The difference surfaces at 2 AM when you're debugging a ClassCastException
// versus not debugging anything because the code didn't compile.

Hibernate's HQL and JPQL operate on strings. The query "SELECT o FROM Order o JOIN FETCH o.items" is a string that Hibernate parses at runtime. If you misspell a field name, you discover it when the query executes. If you cast a result to the wrong type, you discover it when the cast fails. If you refactor an entity and miss a query, you discover it when the CI tests run — or worse, when the code reaches production.

jOOQ's code-generated DSL operates on typed references. ORDERS.ID is a Field<Long>. ORDER_ITEMS.PRICE is a Field<BigDecimal>. The Records.mapping(OrderItemDto::new) call binds these fields to the record constructor's parameter types at compile time. If the field types do not match the constructor parameters, the code does not compile. If you add a column to the SELECT and forget to update the DTO, the code does not compile. If you reorder columns in the subquery, the constructor binding shifts — and the code does not compile.

This trade-off — more verbosity for more safety — is the kind of trade-off I find agreeable. Verbosity costs keystrokes. Runtime type errors cost debugging sessions. The exchange rate favors the verbose approach, particularly in applications with dozens of MULTISET queries where a schema change must propagate correctly through every one of them.

I should note that jOOQ achieves this through code generation from the database schema. The ORDERS, ORDER_ITEMS, and CUSTOMERS references are generated classes that reflect the actual PostgreSQL tables. If you add a column to the database, regenerate the jOOQ classes, and the compiler tells you everywhere that column could be used but is not. This is a fundamentally different feedback loop than Hibernate's runtime discovery of schema mismatches.

Nested MULTISET: where the advantage compounds

The real power becomes visible with two or more levels of nesting. Orders with items, items with tags — the exact scenario that produces Cartesian products in JOIN FETCH.

// Two levels of nesting — orders with items, items with tags
var result = ctx.select(
        ORDERS.ID,
        ORDERS.TOTAL,
        multiset(
            select(
                ORDER_ITEMS.ID,
                ORDER_ITEMS.QUANTITY,
                ORDER_ITEMS.PRICE,
                ORDER_ITEMS.PRODUCT_NAME,
                multiset(
                    select(TAGS.LABEL, TAGS.COLOR)
                    .from(ITEM_TAGS)
                    .join(TAGS).on(TAGS.ID.eq(ITEM_TAGS.TAG_ID))
                    .where(ITEM_TAGS.ITEM_ID.eq(ORDER_ITEMS.ID))
                ).as("tags").convertFrom(r -> r.map(Records.mapping(TagDto::new)))
            )
            .from(ORDER_ITEMS)
            .where(ORDER_ITEMS.ORDER_ID.eq(ORDERS.ID))
        ).as("items").convertFrom(r -> r.map(Records.mapping(OrderItemWithTagsDto::new)))
    )
    .from(ORDERS)
    .where(ORDERS.STATUS.eq("COMPLETED"))
    .orderBy(ORDERS.CREATED_AT.desc())
    .limit(100)
    .fetch();

// With Hibernate JOIN FETCH on this same shape:
// 100 orders x 8 items x 3 tags = 2,400 flat rows.
//
// With MULTISET: 100 rows. Each row carries its items
// as JSON, and each item carries its tags as nested JSON.
// The LIMIT applies to orders, not to the Cartesian product.

The generated SQL nests correlated subqueries inside correlated subqueries:

-- The SQL for nested MULTISET (simplified):
SELECT
  orders.id,
  orders.total,
  (SELECT coalesce(json_agg(json_array(
    t.id, t.quantity, t.price, t.product_name,
    (SELECT coalesce(json_agg(json_array(
      t2.label, t2.color
    )), json_array())
    FROM (
      SELECT tags.label, tags.color
      FROM item_tags
      JOIN tags ON tags.id = item_tags.tag_id
      WHERE item_tags.item_id = t.id
    ) AS t2)
  )), json_array())
  FROM (
    SELECT order_items.id, order_items.quantity,
           order_items.price, order_items.product_name
    FROM order_items
    WHERE order_items.order_id = orders.id
  ) AS t) AS items
FROM orders
WHERE orders.status = 'COMPLETED'
ORDER BY orders.created_at DESC
LIMIT 100;

-- Correlated subqueries inside correlated subqueries.
-- Sounds expensive. It is not — with proper indexes.

This looks expensive. Correlated subqueries have a reputation in the SQL world — the kind of reputation that gets queries rewritten into JOINs by well-meaning reviewers during code review. "Never use correlated subqueries" appears in SQL style guides with the confidence of received wisdom. But that reputation comes from a specific context: correlated subqueries that perform sequential scans on large tables. In that context, the advice is sound. In the context of indexed lookups returning small result sets — which is precisely what MULTISET produces — the advice is misleading.

With proper indexes, each subquery execution is a single index lookup returning a handful of rows. The cost is O(outer_rows * index_lookup_cost), which is the same cost structure as a nested loop join — except without the row duplication on the output side. PostgreSQL's planner understands this cost structure and optimizes accordingly.

Consider the scaling characteristics. With JOIN FETCH, adding a third level of nesting (say, tag categories on each tag) would multiply the row count again: 500 orders x 8 items x 3 tags x 2 categories = 24,000 rows. With MULTISET, the row count remains 500. Each additional nesting level adds execution time — the subqueries must still execute — but does not multiply the result set size. The difference between multiplicative scaling and additive scaling is the difference between a query that degrades with data volume and one that remains bounded.

MULTISET with pagination: the problem that solves itself

Pagination is the test case that most clearly separates MULTISET from JOIN FETCH. The contrast is instructive.

-- Hibernate JOIN FETCH with pagination: the famous trap
-- "Give me page 3 of orders (25 per page) with their items."

-- What you write:
em.createQuery("SELECT o FROM Order o JOIN FETCH o.items WHERE o.status = :status", Order.class)
  .setParameter("status", "COMPLETED")
  .setFirstResult(50)   -- offset
  .setMaxResults(25)    -- limit

-- What Hibernate actually does (WARNING logged):
-- "HHH90003004: firstResult/maxResults specified with collection fetch;
--  applying in memory"
--
-- Hibernate fetches ALL 500 orders with ALL 4,000 items,
-- then discards 475 orders in Java.
-- The database did all the work. The application threw most of it away.

-- Alternative: Hibernate applies LIMIT to the SQL directly:
-- SELECT o.*, i.* FROM orders o
-- JOIN order_items i ON i.order_id = o.id
-- WHERE o.status = 'COMPLETED'
-- OFFSET 50 LIMIT 25
--
-- This returns 25 flattened rows — which might represent
-- 3 complete orders plus partial items from a 4th.
-- Neither option is correct.

The in-memory pagination path is particularly insidious. It works correctly — the results are right — which means it passes every integration test. The performance problem surfaces only at scale, when the query returns 50,000 orders with 400,000 items and the JVM allocates 2 GB of heap to hold a result set that will be 99.5% discarded. The test suite does not catch this because the test database has 12 orders.

MULTISET does not have a pagination problem because MULTISET does not conflate parent rows with child data.

-- jOOQ MULTISET with pagination: it just works.
var page3 = ctx.select(
        ORDERS.ID,
        ORDERS.TOTAL,
        multiset(
            select(ORDER_ITEMS.ID, ORDER_ITEMS.PRODUCT_NAME, ORDER_ITEMS.PRICE)
            .from(ORDER_ITEMS)
            .where(ORDER_ITEMS.ORDER_ID.eq(ORDERS.ID))
        ).as("items").convertFrom(r -> r.map(Records.mapping(OrderItemDto::new)))
    )
    .from(ORDERS)
    .where(ORDERS.STATUS.eq("COMPLETED"))
    .orderBy(ORDERS.CREATED_AT.desc())
    .offset(50)
    .limit(25)
    .fetch();

-- Generated SQL:
-- SELECT orders.id, orders.total,
--   (SELECT coalesce(json_agg(...), '[]'::json) FROM ...)
-- FROM orders
-- WHERE orders.status = 'COMPLETED'
-- ORDER BY orders.created_at DESC
-- OFFSET 50 LIMIT 25;
--
-- OFFSET and LIMIT apply to the orders table.
-- 25 orders returned. Each carries its full item list as JSON.
-- No in-memory pagination. No partial results.
-- No warning logged. Nothing to warn about.

The OFFSET and LIMIT clauses apply to the parent query — the orders table — exactly as you would expect. PostgreSQL skips 50 rows, returns 25, and for each of those 25 rows, the correlated subquery fetches the complete item list. The rows beyond the LIMIT are never processed. Their items are never fetched. Their subqueries never execute. The cost is proportional to the page size, not the total result set.

This is not an optimization. It is the natural consequence of a query structure where the parent rows and child data are separate concerns. When you stop flattening everything into a single JOIN, pagination simply works.

EXPLAIN ANALYZE: MULTISET vs. JOIN FETCH

Numbers settle arguments. Here are the query plans for both approaches on the same dataset: 500 orders, 4,000 items, 12,000 item-tag pairs.

MULTISET (correlated subqueries with json_agg)

-- EXPLAIN ANALYZE on the nested MULTISET query
-- 500 orders, 4,000 items, 12,000 item-tag pairs

QUERY PLAN
─────────────────────────────────────────────────────────────
 Limit  (cost=0.00..4821.50 rows=100 width=76)
        (actual time=0.412..18.741 rows=100 loops=1)
   ->  Seq Scan on orders  (cost=0.00..24107.50 rows=500 width=76)
              (actual time=0.410..18.692 rows=100 loops=1)
         Filter: (status = 'COMPLETED')
         SubPlan 1  -- customer name scalar subquery
           ->  Index Scan using customers_pkey on customers
                 (cost=0.28..8.29 rows=1 width=32)
                 (actual time=0.004..0.004 rows=1 loops=100)
                 Index Cond: (id = orders.customer_id)
         SubPlan 2  -- items MULTISET
           ->  Index Scan using order_items_order_id_idx on order_items
                 (cost=0.29..12.40 rows=8 width=48)
                 (actual time=0.008..0.031 rows=8 loops=100)
                 Index Cond: (order_id = orders.id)
                 SubPlan 3  -- tags nested inside items
                   ->  Nested Loop  (cost=0.56..16.82 rows=3 width=40)
                          (actual time=0.005..0.012 rows=3 loops=800)
                         ->  Index Scan using item_tags_item_id_idx on item_tags
                               (cost=0.28..8.32 rows=3 width=4)
                               (actual time=0.002..0.004 rows=3 loops=800)
                               Index Cond: (item_id = order_items.id)
                         ->  Index Scan using tags_pkey on tags
                               (cost=0.28..2.83 rows=1 width=40)
                               (actual time=0.001..0.001 rows=1 loops=2400)
                               Index Cond: (id = item_tags.tag_id)
 Planning Time: 1.284 ms
 Execution Time: 19.102 ms

-- 19ms for 100 orders with nested items and tags.
-- 100 rows returned. Not 2,400. Not 12,000.
-- Each SubPlan runs with index scans. The memoize node
-- (PG14+) caches repeated subplan results automatically.

19 milliseconds. 100 rows returned. Each SubPlan executes with index scans. The loops count reflects the correlated execution — 100 outer rows drive 100 subplan executions for items, and 800 item rows drive 2,400 subplan executions for tags. Every one of those executions hits an index. Total time: 19ms.

Note the nesting in the plan. SubPlan 3 (tags) runs inside SubPlan 2 (items), which runs inside the outer Seq Scan on orders. This is the correlated subquery chain that conventional wisdom says to avoid. And yet each index scan completes in 0.001-0.008ms. The total cost of 2,400 tag lookups is approximately 2.4ms. The total cost of 800 item lookups is approximately 6.4ms. The sequential scan on orders contributes the remaining time. The plan is efficient not despite the correlated subqueries but because of them — each subquery retrieves precisely the data it needs and nothing more.

Hibernate JOIN FETCH (flat Cartesian JOIN)

-- EXPLAIN ANALYZE on the equivalent Hibernate JOIN FETCH
-- Same data: 500 orders, 4,000 items, 12,000 item-tag pairs

QUERY PLAN
─────────────────────────────────────────────────────────────
 Hash Join  (cost=892.50..28941.00 rows=12000 width=312)
       (actual time=8.421..2841.092 rows=12000 loops=1)
   Hash Cond: (item_tags.tag_id = tags.id)
   ->  Hash Join  (cost=412.50..14208.00 rows=4000 width=248)
             (actual time=4.102..1420.441 rows=4000 loops=1)
         Hash Cond: (order_items.order_id = orders.id)
         ->  Seq Scan on order_items
               (cost=0.00..72.00 rows=4000 width=48)
               (actual time=0.012..2.841 rows=4000 loops=1)
         ->  Hash  (cost=318.00..318.00 rows=500 width=200)
                   (actual time=3.892..3.892 rows=500 loops=1)
               ->  Hash Join  (cost=18.50..318.00 rows=500 width=200)
                     ->  Seq Scan on orders
                           Filter: (status = 'COMPLETED')
                     ->  Hash  (cost=12.00..12.00 rows=500 width=32)
                         ->  Seq Scan on customers
   ->  Hash  (cost=380.00..380.00 rows=12000 width=64)
             (actual time=4.108..4.108 rows=12000 loops=1)
         ->  Hash Join on item_tags/tags
               (actual time=0.892..3.241 rows=12000 loops=1)

 Planning Time: 2.412 ms
 Execution Time: 2847.201 ms

-- 2.8 seconds. 12,000 rows returned to the application.
-- Hibernate deduplicates in memory.
-- The application received 12x the data it needed.

2.8 seconds. 12,000 rows returned. Hash joins everywhere — PostgreSQL builds hash tables for each relation, then probes them. The result set carries 11,500 duplicated rows that Hibernate will discard after transfer.

The hash join strategy is PostgreSQL being optimal for the query it received. A four-way JOIN producing 12,000 rows is best served by hash joins — they minimize the per-row comparison cost when the result set is large. The problem is not the plan. The problem is the query. PostgreSQL was asked to produce 12,000 flat rows, and it produced them as efficiently as possible. It was simply asked the wrong question.

That is a 147x difference on a modest dataset. The gap widens with data volume: more items per order, more tags per item, more orders in the result set. The Cartesian approach scales as the product of cardinalities. The MULTISET approach scales as the sum.

MULTISET is the only approach that achieves all three: single query, no Cartesian product, correct LIMIT. It is not a compromise between JOIN FETCH and @BatchSize. It is a fundamentally different strategy — one that uses PostgreSQL's aggregation capabilities to return structured data instead of flat rows.

JSON vs. JSONB for MULTISET aggregation

A question that arises with some regularity: should jOOQ's MULTISET use jsonb_agg instead of json_agg? The answer is no, and the reasoning is worth understanding because it clarifies a common misunderstanding about PostgreSQL's two JSON types.

-- JSON vs JSONB for MULTISET aggregation on PostgreSQL
-- jOOQ uses json_agg / json_array by default. Should you switch to jsonb?

-- json_agg: preserves input order, no parsing overhead on write.
-- PostgreSQL writes the text as-is. Fast for MULTISET because the
-- result is consumed immediately — never indexed, never queried.

-- jsonb_agg: parses into binary, deduplicates keys, sorts.
-- Useful for storage and indexing. Wasteful for transient aggregation.

-- Benchmark: 500 orders x 8 items each
-- json_agg:  avg 14.2ms
-- jsonb_agg: avg 21.8ms  (53% slower, all overhead is parsing)

-- Recommendation: leave jOOQ's default (json_agg) alone.
-- JSONB is for data you store and query. MULTISET results are
-- ephemeral — serialized in PostgreSQL, deserialized in Java,
-- then discarded. json is correct here.

PostgreSQL's json type stores the text representation verbatim. No parsing, no binary conversion, no key deduplication, no sorting. The data goes in as text and comes out as text. The jsonb type parses the input into a decomposed binary format optimized for storage, indexing, and querying. It supports operators like @>, ?, and #> for efficient in-database JSON operations. It can be indexed with GIN for fast lookups. These are powerful capabilities.

MULTISET results need none of them. They are ephemeral — generated inside PostgreSQL as part of query execution, serialized into the wire protocol, deserialized in the JVM's jOOQ runtime, and immediately discarded. They are never stored in a table. Never indexed. Never queried inside PostgreSQL. The binary decomposition that makes JSONB valuable for stored data is pure computational overhead for transient aggregation results.

In benchmarks on 500 orders with 8 items each, json_agg averages 14.2ms and jsonb_agg averages 21.8ms — a 53% penalty for a format advantage you never use. The overhead comes from JSONB's parsing step: converting each JSON value into a binary tree representation, deduplicating keys, and sorting. All of this work is immediately undone when the result crosses the wire and is parsed again in Java.

jOOQ's default is correct. Leave it alone. If someone on your team opens a pull request changing the aggregation function to jsonb_agg because "JSONB is faster than JSON in PostgreSQL," the correct response is: faster for queries against stored data, slower for transient aggregation. Different tools for different jobs.

PostgreSQL 14+ memoize: when correlated subqueries get a cache

PostgreSQL 14 introduced the Memoize node — an automatic cache for parameterized subplan results. When enabled (it is on by default), PostgreSQL caches the output of a correlated subquery keyed on its parameters. If the same parameter value appears again, the cached result is returned without re-executing the subquery.

-- PostgreSQL 14+ enable_memoize and correlated subqueries
-- The memoize node caches results of parameterized subplans.

-- Before PG14 (or with enable_memoize = off):
SubPlan 2
  ->  Index Scan using order_items_order_id_idx on order_items
        (actual time=0.008..0.031 rows=8 loops=100)
-- Each of the 100 outer rows triggers a fresh index scan.
-- Total: 100 index scans on order_items.

-- With PG14+ and enable_memoize = on (default):
SubPlan 2
  ->  Memoize  (cost=0.29..12.40 rows=8 width=48)
        (actual time=0.002..0.008 rows=8 loops=100)
        Cache Key: orders.id
        Hits: 0  Misses: 100  Evictions: 0
-- For MULTISET, the cache hit rate depends on whether
-- different outer rows share the same subquery parameters.
-- Orders have unique IDs, so hits = 0 here. But consider:

-- A product catalog where many items share the same category:
SubPlan 4
  ->  Memoize  (cost=0.28..4.30 rows=1 width=32)
        (actual time=0.001..0.001 rows=1 loops=4000)
        Cache Key: order_items.category_id
        Hits: 3,700  Misses: 300  Evictions: 0
-- 300 unique categories across 4,000 items.
-- 3,700 cache hits = 3,700 index scans avoided.
-- Memoize turned O(N) subplan executions into O(distinct_keys).

-- Check if memoize is active:
SHOW enable_memoize;  -- 'on' by default in PG14+

For MULTISET queries where each outer row has a unique key (orders with unique IDs), Memoize provides no benefit — every execution is a cache miss. The index scans are already fast enough that the overhead is negligible. Memoize adds a small hash lookup cost per execution, but the cost is measured in nanoseconds and is invisible in practice.

Where Memoize shines is in nested subqueries with repeated parameters. A product catalog where 4,000 items reference only 300 unique categories — the category lookup subquery executes 4,000 times but only performs 300 actual index scans. The other 3,700 are cache hits, each costing effectively zero. The planner knows the statistics — it estimates the number of distinct values from pg_statistic and decides whether Memoize is worth the cache overhead. For high-repetition parameters, it almost always is.

This means MULTISET performance on PostgreSQL 14+ can be significantly better than the raw loop count suggests. The planner's cost estimates account for the expected cache hit rate, and in practice, the Memoize node turns many O(N) subplan executions into O(distinct_keys). For data models with shared reference data — categories, statuses, types, countries — this is a substantial optimization that happens automatically.

If you are on PostgreSQL 13 or earlier, MULTISET still works — but you lose this optimization. The correlated subqueries execute once per outer row regardless. For unique-key lookups this makes no practical difference. For repeated-parameter lookups, the difference can be significant. Upgrading to PG14+ is worth doing for Memoize alone, and it brings several other planner improvements that benefit correlated subquery workloads.

The indexes that make MULTISET fast

MULTISET's performance depends entirely on indexes. I cannot emphasize this sufficiently. Each correlated subquery executes once per outer row (or once per distinct key with Memoize). If those executions hit index scans, each one costs a fraction of a millisecond. If they hit sequential scans, each one costs several milliseconds — and 500 of them cost several seconds.

The difference between a 19ms MULTISET query and a 4-second MULTISET query is not the query. It is the indexes.

-- Indexes that make MULTISET correlated subqueries fast

-- 1. Foreign key columns used in WHERE of correlated subqueries
CREATE INDEX order_items_order_id_idx ON order_items (order_id);
CREATE INDEX item_tags_item_id_idx ON item_tags (item_id);

-- 2. Covering indexes to avoid heap fetches in the subquery
CREATE INDEX order_items_order_id_covering_idx
  ON order_items (order_id)
  INCLUDE (id, quantity, price, product_name);
-- The subquery can be answered entirely from the index.
-- No heap fetch needed. This is an index-only scan.

-- 3. The parent table's filter column
CREATE INDEX orders_status_idx ON orders (status)
  WHERE status = 'COMPLETED';
-- Partial index for the common filter predicate.

-- Without these indexes, each correlated subquery
-- triggers a sequential scan. On 500 outer rows,
-- that is 500 sequential scans of the order_items table.
-- With them, each is a single index lookup.

The pattern is consistent: every foreign key column that appears in a WHERE clause of a correlated subquery needs an index. For MULTISET queries, that means every column used in .where(CHILD.PARENT_ID.eq(PARENT.ID)).

Covering indexes — using INCLUDE to add the selected columns — eliminate heap fetches entirely. The subquery reads only from the index, never touching the table's heap pages. On high-throughput queries, this can halve the I/O cost because the data pages remain untouched in the buffer cache while the index pages are accessed in a tight, predictable pattern. For MULTISET subqueries that select three or four columns from a child table, a covering index often fits within PostgreSQL's default page size with room to spare.

A word about partial indexes: the parent table's filter column (e.g., orders.status) benefits from a partial index when one filter value dominates. WHERE status = 'COMPLETED' on a table where 80% of orders are completed is not selective enough to justify a B-tree lookup — PostgreSQL will prefer a sequential scan. But WHERE status = 'PENDING' on the same table, where only 2% of orders are pending, is an excellent candidate for a partial index. Know your data distribution before creating partial indexes. SELECT status, count(*) FROM orders GROUP BY status takes one second and saves hours of misguided indexing.

Most PostgreSQL installations already have indexes on foreign key columns, either explicitly or as a side effect of application framework migrations. But PostgreSQL, unlike MySQL, does not create indexes on foreign key columns automatically. If your migration says ADD CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id) without a corresponding CREATE INDEX, the foreign key constraint exists but the index does not. The constraint enforces referential integrity. The index enables fast lookups. They are independent concerns, and the absence of one does not imply the presence of the other. I have encountered this gap in a considerable number of production databases.

Composing queries per endpoint: the MULTISET design advantage

One of the subtler benefits of jOOQ's approach — easy to overlook, difficult to overstate — is that the loading strategy lives in the query, not in the entity.

// Conditional MULTISET — load collections only when needed
// Not every endpoint needs the full object graph.

// Lightweight list: just order summaries
var orderList = ctx.select(
        ORDERS.ID,
        ORDERS.TOTAL,
        ORDERS.STATUS,
        ORDERS.CREATED_AT
    )
    .from(ORDERS)
    .where(ORDERS.STATUS.eq("COMPLETED"))
    .orderBy(ORDERS.CREATED_AT.desc())
    .limit(50)
    .fetch();

// Detail view: one order with everything
var orderDetail = ctx.select(
        ORDERS.ID,
        ORDERS.TOTAL,
        ORDERS.STATUS,
        ORDERS.CREATED_AT,
        field(select(CUSTOMERS.NAME)
            .from(CUSTOMERS)
            .where(CUSTOMERS.ID.eq(ORDERS.CUSTOMER_ID))
        ).as("customer_name"),
        multiset(
            select(
                ORDER_ITEMS.ID,
                ORDER_ITEMS.QUANTITY,
                ORDER_ITEMS.PRICE,
                ORDER_ITEMS.PRODUCT_NAME,
                multiset(
                    select(TAGS.LABEL, TAGS.COLOR)
                    .from(ITEM_TAGS)
                    .join(TAGS).on(TAGS.ID.eq(ITEM_TAGS.TAG_ID))
                    .where(ITEM_TAGS.ITEM_ID.eq(ORDER_ITEMS.ID))
                ).as("tags").convertFrom(r -> r.map(Records.mapping(TagDto::new)))
            )
            .from(ORDER_ITEMS)
            .where(ORDER_ITEMS.ORDER_ID.eq(ORDERS.ID))
        ).as("items").convertFrom(r -> r.map(Records.mapping(OrderItemWithTagsDto::new)))
    )
    .from(ORDERS)
    .where(ORDERS.ID.eq(orderId))
    .fetchOne();

// With Hibernate, eager/lazy loading is declared on the entity.
// Every query that touches Order carries the same loading strategy.
// With jOOQ, the loading strategy is the query. Different endpoints
// compose different SELECTs. The entity has no opinion.

In Hibernate, the entity's annotations determine how data is loaded. @OneToMany(fetch = FetchType.LAZY) means the collection is loaded on first access (triggering an N+1 if you iterate). @BatchSize(size = 25) means it is loaded in batches of 25. These decisions are global to the entity. Every query that touches the Order entity inherits the same loading strategy.

You can override this with EntityGraph or ad-hoc JPQL, but the override is an exception to the entity's declared behavior, not a first-class design pattern. The entity remains the source of truth for loading strategy, and the overrides accumulate as special cases.

In jOOQ, there is no entity. There is a query. The query specifies exactly which columns to select and which collections to nest. A list endpoint selects four columns and no nested data. A detail endpoint selects twelve columns with two levels of MULTISET nesting. A report endpoint selects aggregate counts without any row-level data. Each endpoint composes precisely the query it needs. No global loading strategy. No annotations to override. No lazy loading surprises.

This is the "query is the API" philosophy that jOOQ's creator, Lukas Eder, advocates. It trades the convenience of entity annotations for the precision of explicit queries. Whether you find this trade-off agreeable depends on your tolerance for verbosity and your sensitivity to the unexpected database calls that lazy loading can produce. I find it agreeable. But I am a waiter, and waiters prefer explicit instructions to implicit assumptions.

I should be honest about where MULTISET is not the answer

A waiter who overstates his case is no waiter at all. MULTISET is excellent. It is not universal. There are scenarios where it is the wrong approach, and I would be doing you a disservice to omit them.

Large child collections

MULTISET aggregates child rows into JSON arrays inside PostgreSQL's memory. For moderate collections — 5 to 50 children per parent — this is efficient and well within PostgreSQL's comfort zone. For large collections — 500 or 1,000 children per parent — the JSON arrays grow correspondingly large, consuming backend memory during query execution and producing multi-megabyte result cells that must be serialized across the wire and parsed in the JVM.

-- When MULTISET JSON payloads grow large
-- Consider: an order with 500 line items, each with 20 columns.
-- The JSON array for one order's items: ~50 KB of JSON text.
-- For 100 such orders: 5 MB of JSON embedded in the result set.

-- PostgreSQL builds this JSON in memory during query execution.
-- work_mem governs sort and hash operations, not json_agg —
-- but the aggregation still consumes backend memory.

-- Symptoms of oversized MULTISET payloads:
-- 1. Increased memory pressure on the PostgreSQL backend process
-- 2. Slower serialization/deserialization across the wire
-- 3. GC pressure in the JVM from parsing large JSON strings

-- Mitigation strategies:
-- 1. SELECT only the columns you need in the subquery
SELECT order_items.id, order_items.product_name, order_items.price
-- Not: SELECT order_items.*

-- 2. Add a LIMIT inside the MULTISET subquery for preview use cases
(SELECT coalesce(json_agg(t), '[]'::json)
 FROM (
   SELECT id, product_name, price
   FROM order_items
   WHERE order_id = orders.id
   ORDER BY created_at DESC
   LIMIT 5  -- only first 5 items for the preview card
 ) t) AS recent_items

-- 3. For truly large collections (1000+ rows per parent),
--    consider @BatchSize or a separate query.
--    MULTISET excels at moderate nesting, not bulk transfer.

If your orders routinely have 500 line items, MULTISET will produce 500-element JSON arrays per order. For 100 such orders, that is 50,000 JSON elements embedded in the result set. It works. But @BatchSize with a well-chosen batch size may outperform it, because the batched approach streams child rows individually rather than aggregating them into a single JSON column. Measure both. The crossover point depends on your data shape, network topology, and PostgreSQL's work_mem configuration.

Schema without code generation

jOOQ's type-safe MULTISET depends on code-generated table and column references. If your project does not use jOOQ's code generator — perhaps because you work against a dynamic schema, or because your organization has not adopted jOOQ's build pipeline — you lose the compile-time type checking that makes MULTISET's verbosity worthwhile. You can still write MULTISET queries using string-based column references, but at that point you have the verbosity of jOOQ without the safety, which is an unappealing combination.

Hibernate-centric codebases

Migrating from Hibernate to jOOQ is not a weekend project. If your application has 200 entities with complex mappings, lifecycle callbacks, and second-level cache integration, the migration cost is substantial. MULTISET is a compelling reason to prefer jOOQ for new projects or new modules within existing projects. It is not, by itself, a compelling reason to rewrite a working Hibernate data layer. The performance improvement must justify the migration risk, and in many applications, @BatchSize with appropriate batch sizes is "good enough" to make that justification difficult.

I would suggest a practical middle ground: use jOOQ for the performance-critical read paths — dashboards, list views, search results, reports — where MULTISET's single-query advantage matters most. Keep Hibernate for the write paths where its lifecycle management, dirty checking, and cascade operations earn their complexity. The two can coexist in the same application, sharing the same PostgreSQL connection pool via a shared DataSource. This is not purity. It is pragmatism, and pragmatism keeps the household running.

Databases that are not PostgreSQL

jOOQ's MULTISET translates to different SQL depending on the target database. On PostgreSQL, it uses json_agg() — the approach this article examines. On MySQL, it uses JSON_ARRAYAGG(). On Oracle, it uses JSON_ARRAYAGG() with Oracle-specific syntax. On SQL Server, it uses FOR JSON. The concept is portable. The performance characteristics are not. PostgreSQL's json_agg() is mature, well-optimized, and benefits from the Memoize node on PG14+. Other databases' JSON aggregation functions have their own performance profiles, their own optimizer behaviors, and their own edge cases. Everything in this article applies to PostgreSQL. If you are on MySQL or Oracle, benchmark before you commit.

EXPLAIN ANALYZE: a reading guide for MULTISET plans

If you are going to use MULTISET in production, you will need to read its query plans. They look different from the hash join plans that Hibernate generates, and the SubPlan notation can be disorienting on first encounter. Here is what to look for.

SubPlan numbering. Each correlated subquery appears as a numbered SubPlan. SubPlan 1 might be a scalar subquery (customer name), SubPlan 2 the items MULTISET, SubPlan 3 the tags nested inside items. The numbering is sequential in plan order, not in nesting depth.

Loops count. The loops= field tells you how many times the subplan executed. For SubPlan 2 (items), loops=100 means it ran once per outer row. For SubPlan 3 (tags), loops=800 means it ran once per item across all orders (100 orders x 8 items). Multiply actual time by loops to get the total time spent in that subplan.

Index Scan vs Seq Scan. If any SubPlan shows a Seq Scan, you have a missing index. This is the single most important thing to check. A Seq Scan inside a correlated subquery with loops=500 means 500 sequential scans of the child table. Find the WHERE clause of that subplan and ensure the filtered column has an index.

Memoize node (PG14+). If present, check the Hits and Misses counts. A high hit ratio means the cache is saving significant work. A zero-hit ratio (as with unique parent IDs) means the Memoize overhead is negligible but the cache is not contributing. Neither case is cause for concern — the planner made a reasonable choice either way.

Total time attribution. The top-level Execution Time includes all subplan executions. If the total is 19ms and SubPlan 3 accounts for 0.012ms * 800 loops = 9.6ms, you know that tag lookups are half the execution cost. This guides your optimization: a covering index on item_tags would reduce that 9.6ms, while optimizing the orders Seq Scan (which contributes a few milliseconds) would have less impact.

Where Gold Lapel fits alongside jOOQ

jOOQ generates excellent SQL. MULTISET is, in the considered opinion of this household, the most intelligent approach to nested collection loading available in any ORM or query builder. The queries it produces are clean, efficient, and structurally correct.

The variable is the database beneath them. jOOQ trusts that the indexes exist. It trusts that the planner has accurate statistics. It trusts that work_mem is sufficient for the JSON aggregation. These are reasonable trusts, but they are trusts — not guarantees. And in production systems where schemas evolve, data distributions shift, and new queries appear alongside existing ones, the gap between "the query is correct" and "the database is prepared for the query" can widen without anyone noticing until the pager fires.

// Your jOOQ application sends MULTISET queries to PostgreSQL.
// The correlated subqueries depend on indexes existing on the
// foreign key columns used in their WHERE clauses.

// Gold Lapel observes these correlated subqueries at the wire
// protocol level and detects:
//
// 1. Missing indexes on order_items.order_id, item_tags.item_id
//    → auto-creates them. The next execution hits an index scan
//      instead of a sequential scan.
//
// 2. Repeated aggregate patterns — the same MULTISET shape
//    executed thousands of times per hour
//    → materializes the aggregation as a materialized view,
//      refreshed on a schedule. The subquery reads from the
//      pre-computed view instead of re-aggregating each time.
//
// 3. Suboptimal covering indexes — the subquery selects columns
//    not in the index, forcing heap fetches
//    → extends the index with INCLUDE to enable index-only scans.
//
// jOOQ writes the elegant query.
// Gold Lapel ensures the database is ready for it.

Gold Lapel operates at the PostgreSQL wire protocol level, between jOOQ and the database. It observes the correlated subqueries that MULTISET generates and ensures the indexes they depend on actually exist.

A missing index on order_items.order_id turns a 19ms MULTISET query into a 4-second sequential scan disaster. Gold Lapel detects the pattern — a correlated subquery filtering on an unindexed column, repeated hundreds of times per second — and creates the index. The next execution hits an index scan. No application change. No deployment. No jOOQ configuration. The query was always correct. The database simply was not ready for it.

For repeated aggregate patterns — the same MULTISET shape executing thousands of times per hour with similar parameters — Gold Lapel can materialize the aggregation into a materialized view, refreshed on a schedule. The correlated subquery reads from the pre-computed result instead of re-aggregating from the base table each time.

And for covering index opportunities — where a MULTISET subquery selects columns not present in the existing index, forcing heap fetches on every execution — Gold Lapel extends the index with INCLUDE to enable index-only scans. The subquery stops touching the heap. The buffer cache pressure drops. The query runs faster without any code change.

jOOQ handles the query shape. Gold Lapel handles the query infrastructure. Between them, the N+1 problem — a matter I have addressed at length elsewhere — and the Cartesian product problem both stop being problems you have to think about. One writes the question correctly. The other ensures the household is prepared to answer it.