On Polymorphic Reference Resolution in Postgres
Conserving CPU's cycles ...
SubscribeSign in
On Polymorphic Associations in Postgres<br>or one more time on ORM-systems complexity
Andrei Lepikhov<br>May 24, 2026
Share
OUTER JOIN is a typical plague of ORM-based PostgreSQL configurations: the planner is still relatively poor at optimising it. At the same time, ORM frameworks — and 1C as a prominent example — often generate outer joins from standard templates, which opens the door to targeted optimisations. In this article, I dig into one such template — polymorphic reference resolution: what the pattern is, where it comes from (Rails, Django, Hibernate, Salesforce — not just 1C), how widespread it is, and why its structural properties make it possible to significantly speed up execution.<br>Just for the introduction
Think of the homepage of a typical online store — say, Amazon. Personally, on my homepage, I see ads for several products:<br>An air fryer
Magnesium Citrate + B6
Protein brownies
and so on.
Thinking like a database developer, I can roughly imagine that this page is built from the results of a query like:<br>SELECT name, description FROM products p<br>LEFT JOIN kitchen_appliances ka ON (p.id = ka.id)<br>LEFT JOIN pharmacy f ON (p.id = f.id)<br>LEFT JOIN sports_nutrition sf ON (p.id = sf.id)<br>...<br>ORDER BY p.popularity DESC<br>LIMIT N
Planning such a query efficiently is no easy task — and in my experience, this is confirmed by user reports from the 1C world, since PostgreSQL is currently not rich in LEFT JOIN optimisations. At the same time, the properties of this pattern enable the development of various techniques to improve execution efficiency. I've managed to implement several straightforward optimisations of this template. But first, let's understand what polymorphic references actually are, where they come from, and how common they really are. That's the gap I'm trying to fill with this post.<br>The Pattern
Many real-world data models contain references that can point to one of several target entity types. An order line may reference a physical product, a digital download, a gift card, or a subscription. An activity record in a CRM system may be linked to a contact, a company, or a deal. An audit log entry may refer to any entity in the system.<br>Relational schemas have no built-in mechanism for such references. The most common encoding uses a discriminated foreign key — a pair of columns that jointly identify the target table and the row within it:<br>CREATE TABLE order_lines (<br>id SERIAL PRIMARY KEY,<br>order_id INTEGER NOT NULL REFERENCES orders(id),<br>item_type VARCHAR(20) NOT NULL, -- discriminator<br>item_id INTEGER NOT NULL, -- polymorphic FK<br>quantity INTEGER NOT NULL<br>);
Here item_type may contain 'product', 'gift_card', or 'subscription', and item_id stores the primary key of the corresponding table. No single FOREIGN KEY constraint can enforce referential integrity across all three target tables, so the database engine treats item_id as a plain integer column.<br>To resolve the reference — say, to get the human-readable name of the ordered item — the query must join against each possible target table, gated by the discriminator:<br>SELECT<br>ol.id,<br>COALESCE(p.name, g.name, s.name) AS item_name<br>FROM order_lines ol<br>LEFT JOIN products p ON ol.item_type = 'product'<br>AND ol.item_id = p.id<br>LEFT JOIN gift_cards g ON ol.item_type = 'gift_card'<br>AND ol.item_id = g.id<br>LEFT JOIN subscriptions s ON ol.item_type = 'subscription'<br>AND ol.item_id = s.id;
For each row in order_lines, at most one of the three LEFT JOINs finds a match; the other two return NULLs. As the number of target types grows, so does the fan of LEFT JOINs. This query form can be named the polymorphic reference resolution pattern .<br>Structural invariants
The pattern has a precise structure that distinguishes it from an arbitrary collection of outer joins:<br>1. Mutual exclusion. The discriminator predicates in the join conditions are pairwise disjoint: for any row of the base table, the discriminator predicate of at most one join evaluates to true. In the example above, item_type = 'product', item_type = 'gift_card', and item_type = 'subscription' cannot simultaneously be true for the same row. This guarantees that at most one LEFT JOIN produces a match for each base table row. Note that if a row's discriminator value isn't represented by any join in the query (e.g., item_type = 'coupon' with no corresponding join) or the discriminator is NULL, then no join matches — the row passes through all joins with NULLs in all target table columns. The invariant is "at most one," not "exactly one."<br>2. Inner-side key uniqueness. The join key on each target table's side is its primary key (or at least a unique key). This is verifiable by the query planner via pg_catalog metadata. Combined with mutual exclusion, this guarantees that each LEFT JOIN produces at most one match per base table row — no join duplicates rows.<br>3. Column usage constraint. Every reference to a target table...