Re: Removing unneeded self joins
Hywel Carver <hywel@skillerwhale.com>
Commits
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Remove GUC_NOT_IN_SAMPLE from enable_self_join_elimination
- 717d0e8dd945 18.0 landed
-
Put enable_self_join_elimination into postgresql.conf.sample
- c2d329260cd8 18.0 landed
-
Get rid of ojrelid local variable in remove_rel_from_query()
- e167191dc146 18.0 landed
-
Implement Self-Join Elimination
- fc069a3a6319 18.0 cited
-
Revert: Remove useless self-joins
- d1d286d83c0e 17.0 landed
-
Replace lateral references to removed rels in subqueries
- 466979ef031a 17.0 landed
-
Replace relids in lateral subquery parse tree during SJE
- 489072ab7a9e 17.0 landed
-
Forbid SJE with result relation
- 8c441c082797 17.0 landed
-
Fix misuse of RelOptInfo.unique_for_rels cache by SJE
- 30b4955a4668 17.0 landed
-
Replace the relid in some missing fields during SJE
- a7928a57b9f0 17.0 landed
-
Revert 56-bit relfilenode change and follow-up commits.
- a448e49bcbe4 16.0 cited
-
Stabilize timetz test across DST transitions.
- 4a071afbd056 14.0 cited
-
Speed up finding EquivalenceClasses for a given set of rels
- 3373c7155350 13.0 cited
-
Fix mark-and-restore-skipping test case to not be a self-join.
- 24d08f3c0a1f 12.0 landed
The following review has been posted through the commitfest application:
make installcheck-world: not tested
Implements feature: not tested
Spec compliant: not tested
Documentation: not tested
I've built and tested this, and it seems to function correctly to me. One question I have is whether the added "IS NOT NULL" filters can be omitted when they're unnecessary. Some of the resulting plans included an "IS NOT NULL" filter on a non-nullable column. To be clear, this is still an improvement (to me) without that.
Here's the simple test script I ran, on master ("before") and with the latest patch applied ("after").
CREATE TABLE users (id BIGINT PRIMARY KEY, nullable_int BIGINT UNIQUE, some_non_nullable_int BIGINT NOT NULL);
CREATE VIEW only_some_users AS (SELECT * FROM users WHERE id < 10);
CREATE VIEW some_other_users AS (SELECT * FROM users WHERE id > 3);
EXPLAIN SELECT *
FROM users u1
INNER JOIN users u2
ON u1.id = u2.id;
-- before does HASH JOIN
-- after does seq scan with "id IS NOT NULL" condition
EXPLAIN SELECT *
FROM only_some_users
INNER JOIN some_other_users
ON only_some_users.id = some_other_users.id;
-- before does HASH JOIN
-- after does no JOIN, instead does scan, with an extra "id IS NOT NULL condition" (in addition to id < 10, id > 3)
EXPLAIN SELECT *
FROM users u1
INNER JOIN users u2
ON u1.nullable_int = u2.nullable_int;
-- before does HASH JOIN
-- after does scan with (nullable_int IS NOT NULL) filter
EXPLAIN SELECT *
FROM users u1
INNER JOIN users u2
ON u1.id = u2.nullable_int;
-- before does HASH JOIN
-- after correctly unchanged
EXPLAIN SELECT *
FROM users u1
INNER JOIN users u2
ON u1.id = u2.some_non_nullable_int
INNER JOIN users u3
ON u2.some_non_nullable_int = u3.id;
-- before does 2x HASH JOIN
-- now does 1x HASH JOIN, with a sequential scan over users filtered by id IS NOT NULL