pgsql: Fix self-join elimination to avoid inheritance scans.

Started by Tom Laneabout 1 month ago1 messagescommitters
Jump to latest
#1Tom Lane
tgl@sss.pgh.pa.us

Fix self-join elimination to avoid inheritance scans.

Self-join elimination failed to check for inheritance cases,
which has two big problems. First, a unique index on the parent
table isn't sufficient to prove uniqueness across its children too.
Second, since we matched RTEs by OID without considering rte->inh,
we could try to merge "mytable" with "mytable ONLY", which doesn't
even represent the same set of rows.

We can fix both issues by just skipping RTEs that have inh set.
preprocess_relation_rtes already cleared that flag for tables known
not to have inheritance children, so this doesn't break SJE for the
cases where it's valid.

Author: Tender Wang <tndrwang@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: /messages/by-id/CAHewXNn4EjCQEvanN_FPKQ3OJWaBXq8Pxcj-DZYbeQ6_4xSFgw@mail.gmail.com
Backpatch-through: 18

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/89e06d78298ec417cfccd4bd684aa63bcec5ce39

Modified Files
--------------
src/backend/optimizer/plan/analyzejoins.c | 30 +++++++++++++++++++++++-------
1 file changed, 23 insertions(+), 7 deletions(-)