From 3151f16e1874db82ed85a005dac15368903ca9fb Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Wed, 23 Mar 2016 12:28:01 -0400 Subject: [PATCH] postgres_fdw: Fix crash when pushing down multiple joins. A join clause might mention multiple relations on either side, so it need not be the case that a given joinrel's constituent relations are all on one side of the join clause or all on the other. Report by Rajkumar Raghuwanshi. Analysis and fix by Michael Paquier and Ashutosh Bapat. --- .../postgres_fdw/expected/postgres_fdw.out | 252 ++++++++++++++++++ contrib/postgres_fdw/postgres_fdw.c | 5 +- contrib/postgres_fdw/sql/postgres_fdw.sql | 36 +++ 3 files changed, 289 insertions(+), 4 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index a7f32f3bac..96535d4180 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -987,6 +987,34 @@ SELECT t1.c1, t2.c1 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1. 40 | (10 rows) +-- left outer join three tables +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + Limit + Output: t1.c1, t2.c2, t3.c3 + -> Foreign Scan + Output: t1.c1, t2.c2, t3.c3 + Relations: ((public.ft2 t1) LEFT JOIN (public.ft2 t2)) LEFT JOIN (public.ft4 t3) + Remote SQL: SELECT r1."C 1", r2.c2, r4.c3 FROM (("S 1"."T 1" r1 LEFT JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) LEFT JOIN "S 1"."T 3" r4 ON (((r2."C 1" = r4.c1)))) +(6 rows) + +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + c1 | c2 | c3 +----+----+-------- + 11 | 1 | + 12 | 2 | AAA012 + 13 | 3 | + 14 | 4 | AAA014 + 15 | 5 | + 16 | 6 | AAA016 + 17 | 7 | + 18 | 8 | AAA018 + 19 | 9 | + 20 | 0 | AAA020 +(10 rows) + -- left outer join + placement of clauses. -- clauses within the nullable side are not pulled up, but top level clause on -- non-nullable side is pushed into non-nullable side @@ -1060,6 +1088,34 @@ SELECT t1.c1, t2.c1 FROM ft5 t1 RIGHT JOIN ft4 t2 ON (t1.c1 = t2.c1) ORDER BY t2 | 40 (10 rows) +-- right outer join three tables +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + Limit + Output: t1.c1, t2.c2, t3.c3 + -> Foreign Scan + Output: t1.c1, t2.c2, t3.c3 + Relations: ((public.ft4 t3) LEFT JOIN (public.ft2 t2)) LEFT JOIN (public.ft2 t1) + Remote SQL: SELECT r4.c3, r2.c2, r1."C 1" FROM (("S 1"."T 3" r4 LEFT JOIN "S 1"."T 1" r2 ON (((r2."C 1" = r4.c1)))) LEFT JOIN "S 1"."T 1" r1 ON (((r1."C 1" = r2."C 1")))) +(6 rows) + +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + c1 | c2 | c3 +----+----+-------- + 22 | 2 | AAA022 + 24 | 4 | AAA024 + 26 | 6 | AAA026 + 28 | 8 | AAA028 + 30 | 0 | AAA030 + 32 | 2 | AAA032 + 34 | 4 | AAA034 + 36 | 6 | AAA036 + 38 | 8 | AAA038 + 40 | 0 | AAA040 +(10 rows) + -- full outer join EXPLAIN (COSTS false, VERBOSE) SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10; @@ -1088,6 +1144,202 @@ SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1. | 27 (10 rows) +-- full outer join three tables +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + Limit + Output: t1.c1, t2.c2, t3.c3 + -> Foreign Scan + Output: t1.c1, t2.c2, t3.c3 + Relations: ((public.ft2 t1) FULL JOIN (public.ft2 t2)) FULL JOIN (public.ft4 t3) + Remote SQL: SELECT r1."C 1", r2.c2, r4.c3 FROM (("S 1"."T 1" r1 FULL JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) FULL JOIN "S 1"."T 3" r4 ON (((r2."C 1" = r4.c1)))) +(6 rows) + +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + c1 | c2 | c3 +----+----+-------- + 11 | 1 | + 12 | 2 | AAA012 + 13 | 3 | + 14 | 4 | AAA014 + 15 | 5 | + 16 | 6 | AAA016 + 17 | 7 | + 18 | 8 | AAA018 + 19 | 9 | + 20 | 0 | AAA020 +(10 rows) + +-- full outer join + right outer join +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + Limit + Output: t1.c1, t2.c2, t3.c3 + -> Foreign Scan + Output: t1.c1, t2.c2, t3.c3 + Relations: ((public.ft4 t3) LEFT JOIN (public.ft2 t2)) LEFT JOIN (public.ft2 t1) + Remote SQL: SELECT r4.c3, r2.c2, r1."C 1" FROM (("S 1"."T 3" r4 LEFT JOIN "S 1"."T 1" r2 ON (((r2."C 1" = r4.c1)))) LEFT JOIN "S 1"."T 1" r1 ON (((r1."C 1" = r2."C 1")))) +(6 rows) + +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + c1 | c2 | c3 +----+----+-------- + 22 | 2 | AAA022 + 24 | 4 | AAA024 + 26 | 6 | AAA026 + 28 | 8 | AAA028 + 30 | 0 | AAA030 + 32 | 2 | AAA032 + 34 | 4 | AAA034 + 36 | 6 | AAA036 + 38 | 8 | AAA038 + 40 | 0 | AAA040 +(10 rows) + +-- right outer join + full outer join +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + Limit + Output: t1.c1, t2.c2, t3.c3 + -> Foreign Scan + Output: t1.c1, t2.c2, t3.c3 + Relations: ((public.ft2 t2) LEFT JOIN (public.ft2 t1)) FULL JOIN (public.ft4 t3) + Remote SQL: SELECT r2.c2, r1."C 1", r4.c3 FROM (("S 1"."T 1" r2 LEFT JOIN "S 1"."T 1" r1 ON (((r1."C 1" = r2."C 1")))) FULL JOIN "S 1"."T 3" r4 ON (((r2."C 1" = r4.c1)))) +(6 rows) + +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + c1 | c2 | c3 +----+----+-------- + 11 | 1 | + 12 | 2 | AAA012 + 13 | 3 | + 14 | 4 | AAA014 + 15 | 5 | + 16 | 6 | AAA016 + 17 | 7 | + 18 | 8 | AAA018 + 19 | 9 | + 20 | 0 | AAA020 +(10 rows) + +-- full outer join + left outer join +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + Limit + Output: t1.c1, t2.c2, t3.c3 + -> Foreign Scan + Output: t1.c1, t2.c2, t3.c3 + Relations: ((public.ft2 t1) FULL JOIN (public.ft2 t2)) LEFT JOIN (public.ft4 t3) + Remote SQL: SELECT r1."C 1", r2.c2, r4.c3 FROM (("S 1"."T 1" r1 FULL JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) LEFT JOIN "S 1"."T 3" r4 ON (((r2."C 1" = r4.c1)))) +(6 rows) + +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + c1 | c2 | c3 +----+----+-------- + 11 | 1 | + 12 | 2 | AAA012 + 13 | 3 | + 14 | 4 | AAA014 + 15 | 5 | + 16 | 6 | AAA016 + 17 | 7 | + 18 | 8 | AAA018 + 19 | 9 | + 20 | 0 | AAA020 +(10 rows) + +-- left outer join + full outer join +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + Limit + Output: t1.c1, t2.c2, t3.c3 + -> Foreign Scan + Output: t1.c1, t2.c2, t3.c3 + Relations: ((public.ft2 t1) LEFT JOIN (public.ft2 t2)) FULL JOIN (public.ft4 t3) + Remote SQL: SELECT r1."C 1", r2.c2, r4.c3 FROM (("S 1"."T 1" r1 LEFT JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) FULL JOIN "S 1"."T 3" r4 ON (((r2."C 1" = r4.c1)))) +(6 rows) + +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + c1 | c2 | c3 +----+----+-------- + 11 | 1 | + 12 | 2 | AAA012 + 13 | 3 | + 14 | 4 | AAA014 + 15 | 5 | + 16 | 6 | AAA016 + 17 | 7 | + 18 | 8 | AAA018 + 19 | 9 | + 20 | 0 | AAA020 +(10 rows) + +-- right outer join + left outer join +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + Limit + Output: t1.c1, t2.c2, t3.c3 + -> Foreign Scan + Output: t1.c1, t2.c2, t3.c3 + Relations: ((public.ft2 t2) LEFT JOIN (public.ft2 t1)) LEFT JOIN (public.ft4 t3) + Remote SQL: SELECT r2.c2, r1."C 1", r4.c3 FROM (("S 1"."T 1" r2 LEFT JOIN "S 1"."T 1" r1 ON (((r1."C 1" = r2."C 1")))) LEFT JOIN "S 1"."T 3" r4 ON (((r2."C 1" = r4.c1)))) +(6 rows) + +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + c1 | c2 | c3 +----+----+-------- + 11 | 1 | + 12 | 2 | AAA012 + 13 | 3 | + 14 | 4 | AAA014 + 15 | 5 | + 16 | 6 | AAA016 + 17 | 7 | + 18 | 8 | AAA018 + 19 | 9 | + 20 | 0 | AAA020 +(10 rows) + +-- left outer join + right outer join +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Limit + Output: t1.c1, t2.c2, t3.c3 + -> Foreign Scan + Output: t1.c1, t2.c2, t3.c3 + Relations: (public.ft4 t3) LEFT JOIN ((public.ft2 t1) INNER JOIN (public.ft2 t2)) + Remote SQL: SELECT r4.c3, r1."C 1", r2.c2 FROM ("S 1"."T 3" r4 LEFT JOIN ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) ON (((r2."C 1" = r4.c1)) AND ((r1."C 1" = r2."C 1")))) +(6 rows) + +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; + c1 | c2 | c3 +----+----+-------- + 22 | 2 | AAA022 + 24 | 4 | AAA024 + 26 | 6 | AAA026 + 28 | 8 | AAA028 + 30 | 0 | AAA030 + 32 | 2 | AAA032 + 34 | 4 | AAA034 + 36 | 6 | AAA036 + 38 | 8 | AAA038 + 40 | 0 | AAA040 +(10 rows) + -- full outer join + WHERE clause, only matched rows EXPLAIN (COSTS false, VERBOSE) SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1 = t2.c1 OR t1.c1 IS NULL) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 9600e3b3c4..f21689e73d 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -727,12 +727,9 @@ get_useful_ecs_for_relation(PlannerInfo *root, RelOptInfo *rel) if (bms_is_subset(relids, restrictinfo->right_ec->ec_relids)) useful_eclass_list = list_append_unique_ptr(useful_eclass_list, restrictinfo->right_ec); - else - { - Assert(bms_is_subset(relids, restrictinfo->left_ec->ec_relids)); + else if (bms_is_subset(relids, restrictinfo->left_ec->ec_relids)) useful_eclass_list = list_append_unique_ptr(useful_eclass_list, restrictinfo->left_ec); - } } return useful_eclass_list; diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 268cafb023..61cbf55ab9 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -343,6 +343,10 @@ SELECT t1.c1, t2.c2, t3.c3 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) JOIN ft4 t EXPLAIN (COSTS false, VERBOSE) SELECT t1.c1, t2.c1 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; SELECT t1.c1, t2.c1 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; +-- left outer join three tables +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; -- left outer join + placement of clauses. -- clauses within the nullable side are not pulled up, but top level clause on -- non-nullable side is pushed into non-nullable side @@ -360,10 +364,42 @@ SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE EXPLAIN (COSTS false, VERBOSE) SELECT t1.c1, t2.c1 FROM ft5 t1 RIGHT JOIN ft4 t2 ON (t1.c1 = t2.c1) ORDER BY t2.c1, t1.c1 OFFSET 10 LIMIT 10; SELECT t1.c1, t2.c1 FROM ft5 t1 RIGHT JOIN ft4 t2 ON (t1.c1 = t2.c1) ORDER BY t2.c1, t1.c1 OFFSET 10 LIMIT 10; +-- right outer join three tables +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; -- full outer join EXPLAIN (COSTS false, VERBOSE) SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10; SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10; +-- full outer join three tables +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; +-- full outer join + right outer join +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; +-- right outer join + full outer join +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; +-- full outer join + left outer join +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; +-- left outer join + full outer join +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; +-- right outer join + left outer join +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; +-- left outer join + right outer join +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; +SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; -- full outer join + WHERE clause, only matched rows EXPLAIN (COSTS false, VERBOSE) SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1 = t2.c1 OR t1.c1 IS NULL) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- 2.40.0