The Rewrite and Its Limits
A WHERE clause that combines two conditions with OR can, in some cases, be rewritten as a longer query using UNION with much better performance. Whether that rewrite is legal depends on the shape of the query and the data it returns — which is the reason PostgreSQL does not apply it on its own.
The starting point is a query that scales badly as the tables grow:
|
1 2 3 4 |
SELECT id, a.a_val, b.b_val FROM a JOIN b USING (id) WHERE a.id = 42 OR b.id = 42; |
Writing the OR as a UNION instead gives the planner two independently optimizable branches:
|
1 2 3 4 5 6 7 |
SELECT id, a.a_val, b.b_val FROM a JOIN b USING (id) WHERE a.id = 42 UNION SELECT id, a.a_val, b.b_val FROM a JOIN b USING (id) WHERE b.id = 42; |
If both WHERE conditions are selective, indexes on a.id and b.id let each branch of the UNION run as a fast nested loop join.
Where the Transformation Breaks
The rewritten form is no universal substitute. Consider a variant of the query in which the two conditions do not share a unique identifier:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE a ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, x integer, p integer ); CREATE TABLE b ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, x integer, q integer ); INSERT INTO a (x, p) VALUES (1, 1), (1, 1), (2, 1); INSERT INTO b (x, q) VALUES (1, 3), (2, 3); |
Expressed with OR, it reads:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT x, a.p, b.q FROM a JOIN b USING (x) WHERE a.p = 1 OR b.q = 3; x | p | q ---+---+--- 1 | 1 | 3 1 | 1 | 3 2 | 1 | 3 (3 rows) |
Rewriting that as a UNION yields a different result set:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT x, a.p, b.q FROM a JOIN b USING (x) WHERE a.p = 1 UNION SELECT x, a.p, b.q FROM a JOIN b USING (x) WHERE b.q = 3; x | p | q ---+---+--- 2 | 1 | 3 1 | 1 | 3 (2 rows) |
The discrepancy comes from UNION deduplicating the result, much as DISTINCT does. The row that legitimately appears once in the OR query is dropped.
The first example avoided this only because of an unstated assumption: id is the primary key of both tables. Once the result set carries a unique identifier, no duplicate rows can exist, and the UNION rewrite is always correct.
Switching to UNION ALL Does Not Fix It
The natural next step is UNION ALL, which performs better and does not remove duplicates:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT x, a.p, b.q FROM a JOIN b USING (x) WHERE a.p = 1 UNION ALL SELECT x, a.p, b.q FROM a JOIN b USING (x) WHERE b.q = 3; x | p | q ---+---+--- 1 | 1 | 3 1 | 1 | 3 2 | 1 | 3 1 | 1 | 3 1 | 1 | 3 2 | 1 | 3 (6 rows) |
That introduces the opposite defect. Any row satisfying both OR conditions appears twice, once per branch. In this example every row meets both conditions, so the entire result set is duplicated. Neither rewrite reproduces the original result.
Why the Optimizer Leaves the Choice to You
Rewriting OR to UNION ALL is safe when no result row satisfies both conditions. That is likely common, but there is no case the database could verify automatically. Rewriting OR to UNION is safe when the original result set contains no duplicates, and the only guarantee of that is a non-NULL unique key from both tables in the SELECT list — a situation that arises often, largely because people select more columns than they need.
In theory the optimizer could test for a non-NULL unique key from both tables. For real base tables such a check would not be too hard; for derived tables, such as the output of a join, it becomes considerably more difficult.
Such a change would weigh on planning generally. Every query with an OR in its WHERE condition would need to be examined for:
- a
FROMexpression that joins two relations - two relations that are base tables
- a
SELECTlist containing a non-NULL unique key of both base tables
Many queries would pay extra planning time for a benefit that reaches only a few. The rewritten query would also differ radically from the original, and such changes are normally the job of the query rewriter — which cannot rewrite unconditionally, since the original may outperform the rewritten form when the WHERE condition is not selective or the needed indexes are absent.
In Practice
The transformation is a manual one. In most cases you can still apply it: either establish that duplicate rows are irrelevant and use UNION ALL, or establish that duplicates cannot occur and use UNION.
