Optimising Polymorphic Associations in PostgreSQL

Help the planner avoid performance cliffs

Recently, I looked into how common polymorphic associations actually are in relational databases — a performance-hostile pattern built around a discriminated foreign key that ORMs (Rails, Django, Hibernate), CRM platforms (Salesforce), and 1C generate automatically. The front page of a typical online store, or the activity feed of a CRM, is built by exactly this kind of query: a base table is LEFT JOIN-ed to every possible subtype through a (type, id) pair of columns.

That earlier article answered the question 'how widespread is this pattern?' After all, if you're going to improve something, it helps to know how useful the improvement will be, right? Here, I want to give a sense of how this pattern leads to performance regressions and point out directions in the PostgreSQL optimiser that could make the situation easier.

Spoiler: not much yet — but a few things are moving on pgsql-hackers. Three patches, discussed across 2024–2026, target three different sources of regression. Each is covered below.

Where the problems come from

As a reminder, here is what the query looks like:

SELECT
    ol.id,
    COALESCE(p.name, g.name, s.name) AS item_name
FROM order_lines ol
  LEFT JOIN products p
    ON ol.type = 'A' AND ol.item_id = p.id
  LEFT JOIN gift_cards g
    ON ol.type = 'B' AND ol.item_id = g.id
  LEFT JOIN subscriptions s
    ON ol.type = 'C' AND ol.item_id = s.id
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.id = ol.order_id AND o.placed_at >= DATE '2024-01-01')
ORDER BY ol.popularity
LIMIT 100;

At the moment, the Postgres optimiser implements fairly primitive logic here. For every row of the base table (order_lines), the query probes each of the N subtype tables through a LEFT JOIN. Only one of those joins ever returns a match — the one whose discriminator matches the type value in that row. The remaining N−1 joins are guaranteed to come up empty: their ON predicate carries a different discriminator value (see the schema below).

The optimiser picks one scan order for the inner side of the LEFT JOIN and applies it to every row — it cannot route each outer row to the right inner table based on the discriminator value. The baseline cost of the pattern is O(M × N) probes, of which only O(M) are useful. And you genuinely can't avoid touching the inner side of each OUTER JOIN: there is no constraint that would guarantee that key values across the inner tables are non-overlapping. So we have to look for a compromise…

Having gathered some statistics on problematic cases, I settled on the following main causes of query-performance degradation that show up in production:

1. Size of the base table. Doubling the row count of the base table doubles the number of fruitless inner-side join probes.

2. Aggregation of the result (usually ORDER BY + LIMIT). A sort often stalls the pipeline. As a consequence, the LIMIT operator can't reduce the read from the base table and cut the scan of the join tree short. Imagine that out of 100k kinds of items in the order_lines table, the query only needs the TOP-10 most popular — a full scan looks plainly excessive.

3. An EXISTS subquery that the optimiser pulls up into a join. A somewhat non-obvious but painful — and therefore noticeable — case: once the number of tables in the join tree exceeds the join_collapse_limit GUC, the pulled-up SEMI JOIN drops out of the join-order search problem and is moved into a separate one. The upshot is that this EXISTS subquery, which originally served as a row filter on the base-table scan, floats up to the top of the query tree and ends up filtering base-table rows at a far later stage of execution.

4. Accumulating cardinality-estimation error. As the number of joins grows, the estimate quickly bottoms out at the minimum value and doesn't change higher up the plan tree. For our cascade of LEFT JOIN operators, this isn't a huge deal, but it's worth a mention.

Let's go through the ways out.

1. Result Filter, or one-sided gating

In December 2024, a thread was opened on pgsql-hackers titled "Do not scan index in right table if condition for left join evaluates to false using columns in left table". Tom Lane proposed splitting the JOIN ... ON ... conditions into two groups — those that depend only on the outer side and those that depend on the inner side. The first group can be evaluated before starting the inner-side scan of a NestLoop: if they're already false, the scan can be skipped entirely. Andres Freund suggested simplifying the implementation by moving the evaluation of the outer clause into a so-called gating operator — a separate Result plan node.

The need to look at the inner side for each outer row remains — there's still no constraint in the database that would guarantee that, if a row was found for a given item_id in gift_cards, no such row exists in subscriptions. However, since each join clause has the form:

... ON ol.type = 'subscription' AND ol.id = s.id

We can cap the overhead of scanning the inner side by checking the condition on ol.type: if it evaluates to false, there's no reason to reach into the inner side because the inner row would be replaced by a set of NULLs anyway. We end up with something like the following plan shape:

So we add a single gating-expression operator to the plan. It is evaluated for every row of the outer side of that join. That operation is usually cheap enough — it won't touch shared buffers, indexes, or disk. So while formally the reach into the inner side remains, the overhead is driven down to almost nothing. This is especially effective when the inner side isn't a plain table scan but a complex subtree with additional joins and aggregation.

The join tree of our query's plan would then look something like this:

The benefits of this approach are clear:

1. Fewer reaches into shared buffers and disk.

2. Bounded performance impact as the list of categories grows (i.e. the number of joined tables).

3. Protection against regression when the inner-side scan isn't index-based.

2. Sort Pushdown, or pre-sorting

The second scaling problem with such queries is tied to the ORDER BY + LIMIT construct. We can't ignore it because picking the TOP-N most popular items is a core pattern in online stores.

What exactly is the problem? If the base table order_lines has a suitable index and the data already arrives sorted, then the join pipeline runs without a hitch: we pull only the minimum number of rows from order_lines needed to satisfy the LIMIT. But if the data isn't sorted, then every row of order_lines flows through the join tree, and the sort happens higher up — see the figure below:

So why not just build an index and be done with it? First, the need for fast DML and maintenance operations on the database often won't allow for many indexes — each new index is frequently an added overhead. Second, such an index can contain many duplicates, which reduces its efficiency. In our case, for example, if popularity is discrete and spread over some fixed integer scale, having that index may not be worthwhile.

The most effective way out is to insert a sort operation (heapsort, TOP-N) directly above the base-table scan — yes, we read every row from it once, but at least we don't push them through the entire join tree. That is, such a sort reduces the regression as the number of joins in the tree grows (see the figure below).

So why doesn't PostgreSQL do this already? The issue is that query planning happens bottom-up: at the moment, it chooses the optimal scan method, yet the optimiser knows nothing about the structure of the join tree or whether it's worth sorting the scan result on any particular field. And the join above doesn't know it either, since whether the technique pays off depends on the plan's overall structure. Generating plan variants "just in case" is a poor strategy because it enlarges the search space and, therefore, increases memory consumption and planning time.

In April 2026, a patch was proposed on the hackers mailing list: try to build a pre-sorted path for the base table of a NestLoop if that table is mentioned in a prefix of query_pathkeys. The compromise on offer: if a table's columns appear in some prefix of the ORDER BY or GROUP BY operator, there's a fair chance it will be efficient to have a pre-sorted set of rows on that prefix. And that can pay off not only in our query, but in MergeJoin operators too, can make more use of IncrementalSort, and generally give the optimiser a bit more room to choose — after all, we'll have to sort on that prefix anyway, won't we?

3. SubLink relocation, or filtering as early as possible

This problem is harder to grasp. When a query has (say) an EXISTS subquery that can't be transformed into a JOIN, it acts as a filter, reducing the number of rows the table-scan operator produces (see the picture below). Consequently, fewer rows flow through the join tree. If only 1% of order_lines rows pass the EXISTS subquery, then exactly 1% of rows should enter the LEFT JOIN tree, and the remaining work shrinks by a factor of 100.

If, on the other hand, such an EXISTS subquery is transformed into a JOIN, then on one hand this changes how many times the EXISTS is evaluated — instead of evaluating the subquery per base-table row, the number of passes is determined by the join type (for HashJoin and MergeJoin it's usually once). On the other hand, this join is inserted at the top of the join tree — i.e. as far as possible from the base table order_lines (see the figure below). And when join_collapse_limit is smaller than the number of joins, this means the optimiser will never consider the orders ⋈ order_lines variant. That's a potential performance cliff — rows that used to be filtered out early in execution can, after an upgrade or as the join tree grows, suddenly flow higher up the JOIN tree and be filtered much later.

To take the edge off the problem, in May 2026, a patch was proposed on hackers that changes the subquery-to-JOIN transformation logic (the so-called pull-up):

After converting EXISTS into a SEMI JOIN, find a suitable place for it: don’t insert it at the top of the join tree. Instead, push the SEMI JOIN down into the smallest subtree that contains the relations referenced inside that subquery.

In our example, there's only one reference to tables outside the EXISTS subquery — o.id = ol.order_id, which refers only to order_lines. So the newly created SEMI JOIN is inserted into the join tree right next to order_lines.

The resulting join tree looks like this:

And the optimiser gets the chance to choose the join order — including making it roughly what it was before the Subquery → JOIN transformation (if that's efficient).

4. Join statistics

This text wouldn't be complete without mentioning one feature under active development in the community — JOIN selectivity statistics.

Alexandra Wang is developing a working prototype of "join statistics": she's trying to solve one aspect of the long-standing optimiser problem, where optimisers estimate join selectivity from each table’s statistics in isolation and therefore miss skew in the joint distribution of values across the two tables. That error grows up the plan, pushing the planner to pick a nested loop where a hash/merge is needed. The idea of the feature is to collect statistics on the join result itself and feed them into the selectivity estimate.

It's proposed as a declarative thing: the user declares the desired join via CREATE STATISTICS (modelled on ordinary extended statistics). For now, a narrow but useful slice actually works: MCV statistics for an INNER JOIN of two tables on an equality expression. This is still a proof of concept, and the community broadly agrees that the direction is useful. The open questions are the fundamental ones — how to store such statistics in the catalogue so they generalise to joins of more than two tables, and how to collect them honestly.

Wrapping up

The polymorphic query pattern is a natural consequence of mapping object-oriented abstractions onto the relational model. Banning it "from above" is extremely hard: it's generated automatically by ORMs and platforms, and retroactive schema changes in large production systems are prohibitively expensive. So the only practical short-term direction is to have the planner handle it better.

The three directions listed above aim to reduce the non-linear effects that can arise as the database grows or the schema becomes more complex. None of them “closes” the problem entirely — but each removes one specific pain point:

• Result Filter / one-sided gating removes the M × (N−1) fruitless probes.

• Sort Pushdown lets LIMIT localise the disk access when there’s no index on the sort condition.

• SubLink relocation reduces the fallout from the non-cost-based transformation of subqueries (EXISTS, ANY, etc.).

All three are being discussed on pgsql-hackers, and none are committed yet. If you have a workload with the polymorphic pattern — especially with N > 10 LEFT JOINs, or with subqueries in the WHERE clause whose transformation leads to painful effects — I'd be very interested to take a look. Drop a comment, or write straight to pgsql-hackers.

THE END.
June 20, 2026, Madrid.