Modelling polymorphism with UNION ALL
A CRM system, for instance, has to deal with customers and employees, both of which are people sharing attributes such as name and birthday. Object oriented programming models this kind of polymorphism easily; relational databases do not. One common approach is a table per entity, with the "superclass" expressed as a UNION ALL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
CREATE SEQUENCE seq; CREATE TABLE bird ( id bigint PRIMARY KEY DEFAULT nextval('seq'), wingspan real NOT NULL, beak_size double precision NOT NULL ); CREATE TABLE bat ( id bigint PRIMARY KEY DEFAULT nextval('seq'), wingspan numeric NOT NULL, body_temperature numeric NOT NULL ); CREATE TABLE cat ( id bigint PRIMARY KEY DEFAULT nextval('seq'), body_temperature numeric NOT NULL, tail_length numeric ); CREATE VIEW flying_animal AS SELECT id, wingspan FROM bird UNION ALL SELECT id, wingspan FROM bat; CREATE VIEW mammal AS SELECT id, body_temperature FROM bat UNION ALL SELECT id, body_temperature FROM cat; |
Random test data lets us measure how such a view performs:
|
1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO bird (wingspan, beak_size) SELECT 20 + random() * 5, 2 + random() FROM generate_series(1, 1000000); INSERT INTO bat (wingspan, body_temperature) SELECT 15 + random() * 5, 40 + random() * 2 FROM generate_series(1, 1000000); INSERT INTO cat (body_temperature, tail_length) SELECT 36.5 + random(), 20 + random() * 3 FROM generate_series(1, 1000000); |
A surprising performance difference
Look up animals by joining the view against a small lookup table:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE lookup ( id bigint PRIMARY KEY ); INSERT INTO lookup VALUES (42), (500000), (1500000), (1700000), (2500000), (2700000); ANALYZE lookup; |
Now compare join performance across the two views:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
test=> timing Timing is on. test=> SELECT * FROM flying_animal JOIN lookup USING (id); id │ wingspan ═════════╪═══════════ 42 │ 23.711483 500000 │ 24.004992 1500000 │ 15.684511 1700000 │ 18.573671 (4 rows) Time: 384.368 ms test=> SELECT * FROM mammal JOIN lookup USING (id); id │ body_temperature ═════════╪══════════════════ 1500000 │ 41.1855152884075 1700000 │ 40.7127191738703 2500000 │ 36.5138848514496 2700000 │ 36.9444466002649 (4 rows) Time: 1.282 ms |
Something is clearly wrong with the flying animals.
Reading the plans
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM flying_animal JOIN lookup USING (id); QUERY PLAN ═════════════════════════════════════════════════════════════════════════════════════════════ Hash Join (actual time=0.139..542.474 rows=4 loops=1) Hash Cond: (bird.id = lookup.id) -> Append (actual time=0.081..459.626 rows=2000000 loops=1) -> Seq Scan on bird (actual time=0.079..72.049 rows=1000000 loops=1) -> Subquery Scan on '*SELECT* 2' (actual time=0.016..314.671 rows=1000000 loops=1) -> Seq Scan on bat (actual time=0.008..73.920 rows=1000000 loops=1) -> Hash (actual time=0.018..0.019 rows=6 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on lookup (actual time=0.008..0.011 rows=6 loops=1) Planning Time: 0.311 ms Execution Time: 542.533 ms EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM mammal JOIN lookup USING (id); QUERY PLAN ════════════════════════════════════════════════════════════════════════════════════════ Nested Loop (actual time=0.083..0.116 rows=4 loops=1) -> Seq Scan on lookup (actual time=0.008..0.010 rows=6 loops=1) -> Append (actual time=0.016..0.017 rows=1 loops=6) -> Index Scan using bat_pkey on bat (actual time=0.012..0.012 rows=0 loops=6) Index Cond: (id = lookup.id) -> Index Scan using cat_pkey on cat (actual time=0.004..0.004 rows=0 loops=6) Index Cond: (id = lookup.id) Planning Time: 0.183 ms Execution Time: 0.150 ms |
The query on mammal behaves as intended: a nested loop join that uses the primary key indexes on bat and cat to fetch only the few needed rows. The query on flying_animal, by contrast, scans all 2 million rows of bird and bat to perform a hash join.
Why the optimizer gives up
To get the fast plan, PostgreSQL must push the join condition into the UNION ALL, which appears as Append in the execution plan. The slow query instead shows a Subquery Scan on "*SELECT* 2": the branches of a UNION ALL are subqueries, and the optimizer normally pulls subqueries up into the main join tree whenever it can. The relevant logic lives in pull_up_subqueries_recurse() in src/backend/optimizer/prep/prepjointree.c:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
/* * Alternatively, is it a simple UNION ALL subquery? If so, flatten * into an 'append relation'. * * It's safe to do this regardless of whether this query is itself an * appendrel member. (If you're thinking we should try to flatten the * two levels of appendrel together, you're right; but we handle that * in set_append_rel_pathlist, not here.) */ if (rte->rtekind == RTE_SUBQUERY && is_simple_union_all(rte->subquery)) return pull_up_simple_union_all(root, jtnode, rte); |
The comment on is_simple_union_all() provides the operative condition:
|
1 2 3 4 5 |
/* * We require all the setops to be UNION ALL (no mixing) and there can't be * any datatype coercions involved, ie, all the leaf queries must emit the * same datatypes. */ |
That points to the table definitions: wingspan is real in bird but numeric in bat. This data type mismatch is what prevents a proper flattening of the UNION ALL. The unflattened subquery survives, and the optimizer never reaches the ideal plan.
What makes the diagnosis counterintuitive is that the offending column plays no real role in the query — omitting it from the SELECT list still yields the bad plan.
Type resolution in a UNION ALL
Both branches of a UNION need the same column count, and corresponding columns must have compatible data types. Per the documentation, compatibility means that when the types differ, PostgreSQL picks the type for which an implicit conversion — a coercion — exists from the other type. If a conversion exists in both directions, the type from the first UNION branch wins.
Here, numeric casts implicitly to real but not vice versa, so wingspan is real in flying_animals. The view is created without complaint, yet this choice is the source of the performance problem.
Making the types match
An explicit type cast forces identical data types on both sides of the UNION ALL. Redefine the view accordingly:
|
1 2 3 4 |
CREATE OR REPLACE VIEW flying_animal AS SELECT id, wingspan FROM bird UNION ALL SELECT id, wingspan::real FROM bat; |
The fast execution plan returns:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM flying_animal JOIN lookup USING (id); QUERY PLAN ══════════════════════════════════════════════════════════════════════════════════════════ Nested Loop (actual time=7.714..10.593 rows=4 loops=1) -> Seq Scan on lookup (actual time=0.008..0.015 rows=6 loops=1) -> Append (actual time=1.682..1.759 rows=1 loops=6) -> Index Scan using bird_pkey on bird (actual time=1.445..1.446 rows=0 loops=6) Index Cond: (id = lookup.id) -> Index Scan using bat_pkey on bat (actual time=0.308..0.309 rows=0 loops=6) Index Cond: (id = lookup.id) Planning Time: 0.452 ms Execution Time: 10.673 ms |
Takeaway
Allowing slightly different data types on either side of a UNION ALL is convenient, but as shown here it can produce poor execution plans. Apply explicit type casts so that the data types are identical. A smarter optimizer might handle such cases, but the workaround is simple enough.
