On 2017/04/08 4:24, Robert Haas wrote:
Looking at the code itself, I find the changes to joinpath.c rather alarming.
I missed this mail. Sorry about that, Robert.
+ /* Save hashclauses for possible use by the FDW */
+ if (extra->consider_foreignjoin && hashclauses)
+ extra->hashclauses = hashclauses;
A minor consideration is that this is fairly far away from where
hashclauses actually gets populated, so if someone later added an
early "return" statement to this function -- after creating some paths
-- it could subtly break join pushdown. But I also think there's no
real need for this. The loop that extracts hash clauses is simple
enough that we could just refactor it into a separate function, or if
necessary duplicate the logic.
I refactored that into a new function so that we can call that function
at the top of add_paths_to_joinrel and store the result in
JoinPathExtraData.
+ /* Save first mergejoin data for possible use by the FDW */
+ if (extra->consider_foreignjoin && outerkeys == all_pathkeys)
+ {
+ extra->mergeclauses = cur_mergeclauses;
+ extra->outersortkeys = outerkeys;
+ extra->innersortkeys = innerkeys;
+ }
Similarly here. select_outer_pathkeys_for_merge(),
find_mergeclauses_for_pathkeys(), and make_inner_pathkeys_for_merge()
are all extern, so there's nothing to keep CreateLocalJoinPath() from
just doing that work itself instead of getting help from joinpath,
which I guess seems better to me. I think it's just better if we
don't burden joinpath.c with keeping little bits of data around that
CreateLocalJoinPath() can easily get for itself.
Done that way.
There appears to be no regression test covering the case where we get
a Merge Full Join with a non-empty list of mergeclauses. Hash Full
Join is tested, as is Merge Full Join without merge clauses, but
there's no test for Merge Full Join with mergeclauses, and since that
is a separate code path it seems like it should be tested.
Done.
- /*
- * If either inner or outer path is a ForeignPath corresponding to a
- * pushed down join, replace it with the fdw_outerpath, so that we
- * maintain path for EPQ checks built entirely of local join
- * strategies.
- */
- if (IsA(joinpath->outerjoinpath, ForeignPath))
- {
- ForeignPath *foreign_path;
-
- foreign_path = (ForeignPath *) joinpath->outerjoinpath;
- if (IS_JOIN_REL(foreign_path->path.parent))
- joinpath->outerjoinpath = foreign_path->fdw_outerpath;
- }
-
- if (IsA(joinpath->innerjoinpath, ForeignPath))
- {
- ForeignPath *foreign_path;
-
- foreign_path = (ForeignPath *) joinpath->innerjoinpath;
- if (IS_JOIN_REL(foreign_path->path.parent))
- joinpath->innerjoinpath = foreign_path->fdw_outerpath;
- }
This logic is removed and not replaced with anything, but I don't see
what keeps this code...
+ Path *outer_path = outerrel->cheapest_total_path;
+ Path *inner_path = innerrel->cheapest_total_path;
...from picking a ForeignPath?
CreateLocalJoinPath creates an alternative local join path for a foreign
join from the cheapest total paths for the outer/inner relations. The
reason for the above is to pass these paths to that function. On second
thought, however, I think it would be convenient for the caller to just
pass outerrel/innerrel to that function. So, I modified that function's
API as such. Another change is: the previous version of that function
allowed the caller to create a parameterized local-join path
corresponding to a parameterized foreign join, but that is a feature,
not a bug fix, so I dropped that. (I'll propose that as part of the
patch in [1].)
There's probably more to think about here, but those are my question
on an initial read-through.
Thanks for the review!
Attached is an updated version of the patch.
Best regards,
Etsuro Fujita
[1] https://commitfest.postgresql.org/14/1042/
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 1701,1722 **** SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 =
t2.c1) ORDER BY t1.c3, t
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
Remote SQL: SELECT r1."C 1", r1.c3, CASE WHEN (r1.*)::text IS
NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8)
END, r2."C 1", CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2,
r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN
"S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST,
r1."C 1" ASC NULLS LAST FOR UPDATE OF r1
! -> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
! Merge Cond: (t1.c1 = t2.c1)
! -> Sort
Output: t1.c1, t1.c3, t1.*
! Sort Key: t1.c1
! -> Foreign Scan on public.ft1 t1
! Output: t1.c1, t1.c3, t1.*
! Remote SQL: SELECT "C 1", c2, c3, c4, c5,
c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE
! -> Sort
Output: t2.c1, t2.*
! Sort Key: t2.c1
! -> Foreign Scan on public.ft2 t2
! Output: t2.c1, t2.*
! Remote SQL: SELECT "C 1", c2, c3, c4, c5,
c6, c7, c8 FROM "S 1"."T 1"
! (23 rows)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY
t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE OF t1;
c1 | c1
--- 1701,1716 ----
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
Remote SQL: SELECT r1."C 1", r1.c3, CASE WHEN (r1.*)::text IS
NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8)
END, r2."C 1", CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2,
r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN
"S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST,
r1."C 1" ASC NULLS LAST FOR UPDATE OF r1
! -> Nested Loop
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
! Join Filter: (t1.c1 = t2.c1)
! -> Foreign Scan on public.ft1 t1
Output: t1.c1, t1.c3, t1.*
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7,
c8 FROM "S 1"."T 1" FOR UPDATE
! -> Foreign Scan on public.ft2 t2
Output: t2.c1, t2.*
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7,
c8 FROM "S 1"."T 1"
! (17 rows)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY
t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE OF t1;
c1 | c1
***************
*** 1745,1766 **** SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 =
t2.c1) ORDER BY t1.c3, t
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
Remote SQL: SELECT r1."C 1", r1.c3, CASE WHEN (r1.*)::text IS
NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8)
END, r2."C 1", CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2,
r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN
"S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST,
r1."C 1" ASC NULLS LAST FOR UPDATE OF r1 FOR UPDATE OF r2
! -> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
! Merge Cond: (t1.c1 = t2.c1)
! -> Sort
Output: t1.c1, t1.c3, t1.*
! Sort Key: t1.c1
! -> Foreign Scan on public.ft1 t1
! Output: t1.c1, t1.c3, t1.*
! Remote SQL: SELECT "C 1", c2, c3, c4, c5,
c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE
! -> Sort
Output: t2.c1, t2.*
! Sort Key: t2.c1
! -> Foreign Scan on public.ft2 t2
! Output: t2.c1, t2.*
! Remote SQL: SELECT "C 1", c2, c3, c4, c5,
c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE
! (23 rows)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY
t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE;
c1 | c1
--- 1739,1754 ----
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
Remote SQL: SELECT r1."C 1", r1.c3, CASE WHEN (r1.*)::text IS
NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8)
END, r2."C 1", CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2,
r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN
"S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST,
r1."C 1" ASC NULLS LAST FOR UPDATE OF r1 FOR UPDATE OF r2
! -> Nested Loop
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
! Join Filter: (t1.c1 = t2.c1)
! -> Foreign Scan on public.ft1 t1
Output: t1.c1, t1.c3, t1.*
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7,
c8 FROM "S 1"."T 1" FOR UPDATE
! -> Foreign Scan on public.ft2 t2
Output: t2.c1, t2.*
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7,
c8 FROM "S 1"."T 1" FOR UPDATE
! (17 rows)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY
t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE;
c1 | c1
***************
*** 1790,1811 **** SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 =
t2.c1) ORDER BY t1.c3, t
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
Remote SQL: SELECT r1."C 1", r1.c3, CASE WHEN (r1.*)::text IS
NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8)
END, r2."C 1", CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2,
r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN
"S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST,
r1."C 1" ASC NULLS LAST FOR SHARE OF r1
! -> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
! Merge Cond: (t1.c1 = t2.c1)
! -> Sort
Output: t1.c1, t1.c3, t1.*
! Sort Key: t1.c1
! -> Foreign Scan on public.ft1 t1
! Output: t1.c1, t1.c3, t1.*
! Remote SQL: SELECT "C 1", c2, c3, c4, c5,
c6, c7, c8 FROM "S 1"."T 1" FOR SHARE
! -> Sort
Output: t2.c1, t2.*
! Sort Key: t2.c1
! -> Foreign Scan on public.ft2 t2
! Output: t2.c1, t2.*
! Remote SQL: SELECT "C 1", c2, c3, c4, c5,
c6, c7, c8 FROM "S 1"."T 1"
! (23 rows)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY
t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE OF t1;
c1 | c1
--- 1778,1793 ----
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
Remote SQL: SELECT r1."C 1", r1.c3, CASE WHEN (r1.*)::text IS
NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8)
END, r2."C 1", CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2,
r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN
"S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST,
r1."C 1" ASC NULLS LAST FOR SHARE OF r1
! -> Nested Loop
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
! Join Filter: (t1.c1 = t2.c1)
! -> Foreign Scan on public.ft1 t1
Output: t1.c1, t1.c3, t1.*
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7,
c8 FROM "S 1"."T 1" FOR SHARE
! -> Foreign Scan on public.ft2 t2
Output: t2.c1, t2.*
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7,
c8 FROM "S 1"."T 1"
! (17 rows)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY
t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE OF t1;
c1 | c1
***************
*** 1834,1855 **** SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 =
t2.c1) ORDER BY t1.c3, t
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
Remote SQL: SELECT r1."C 1", r1.c3, CASE WHEN (r1.*)::text IS
NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8)
END, r2."C 1", CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2,
r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN
"S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST,
r1."C 1" ASC NULLS LAST FOR SHARE OF r1 FOR SHARE OF r2
! -> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
! Merge Cond: (t1.c1 = t2.c1)
! -> Sort
Output: t1.c1, t1.c3, t1.*
! Sort Key: t1.c1
! -> Foreign Scan on public.ft1 t1
! Output: t1.c1, t1.c3, t1.*
! Remote SQL: SELECT "C 1", c2, c3, c4, c5,
c6, c7, c8 FROM "S 1"."T 1" FOR SHARE
! -> Sort
Output: t2.c1, t2.*
! Sort Key: t2.c1
! -> Foreign Scan on public.ft2 t2
! Output: t2.c1, t2.*
! Remote SQL: SELECT "C 1", c2, c3, c4, c5,
c6, c7, c8 FROM "S 1"."T 1" FOR SHARE
! (23 rows)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY
t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE;
c1 | c1
--- 1816,1831 ----
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
Remote SQL: SELECT r1."C 1", r1.c3, CASE WHEN (r1.*)::text IS
NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8)
END, r2."C 1", CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2,
r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN
"S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST,
r1."C 1" ASC NULLS LAST FOR SHARE OF r1 FOR SHARE OF r2
! -> Nested Loop
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
! Join Filter: (t1.c1 = t2.c1)
! -> Foreign Scan on public.ft1 t1
Output: t1.c1, t1.c3, t1.*
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7,
c8 FROM "S 1"."T 1" FOR SHARE
! -> Foreign Scan on public.ft2 t2
Output: t2.c1, t2.*
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7,
c8 FROM "S 1"."T 1" FOR SHARE
! (17 rows)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY
t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE;
c1 | c1
***************
*** 1866,1871 **** SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 =
t2.c1) ORDER BY t1.c3, t
--- 1842,1943 ----
110 | 110
(10 rows)
+ -- FOR UPDATE/SHARE in situations where a full join is pushed down
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT t1.c1, t2.c1, t3.c1 FROM (SELECT c1 FROM "S 1"."T 3" WHERE c1 = 50) t1
INNER JOIN (ft4 t2 FULL JOIN ft5 t3 ON (t2.c1 = t3.c1)) ON (TRUE) ORDER BY
t1.c1, t2.c1, t3.c1 FOR UPDATE OF t1;
+
QUERY
PLAN
+
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ LockRows
+ Output: "T 3".c1, t2.c1, t3.c1, "T 3".ctid, t2.*, t3.*
+ -> Nested Loop
+ Output: "T 3".c1, t2.c1, t3.c1, "T 3".ctid, t2.*, t3.*
+ -> Foreign Scan
+ Output: t2.c1, t2.*, t3.c1, t3.*
+ Relations: (public.ft4 t2) FULL JOIN (public.ft5 t3)
+ Remote SQL: SELECT r2.c1, CASE WHEN (r2.*)::text IS NOT NULL
THEN ROW(r2.c1, r2.c2, r2.c3) END, r3.c1, CASE WHEN (r3.*)::text IS NOT NULL
THEN ROW(r3.c1, r3.c2, r3.c3) END FROM ("S 1"."T 3" r2 FULL JOIN "S 1"."T 4" r3
ON (((r2.c1 = r3.c1)))) ORDER BY r2.c1 ASC NULLS LAST, r3.c1 ASC NULLS LAST
+ -> Hash Full Join
+ Output: t2.c1, t2.*, t3.c1, t3.*
+ Hash Cond: (t2.c1 = t3.c1)
+ -> Foreign Scan on public.ft4 t2
+ Output: t2.c1, t2.*
+ Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
+ -> Hash
+ Output: t3.c1, t3.*
+ -> Foreign Scan on public.ft5 t3
+ Output: t3.c1, t3.*
+ Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T
4"
+ -> Materialize
+ Output: "T 3".c1, "T 3".ctid
+ -> Seq Scan on "S 1"."T 3"
+ Output: "T 3".c1, "T 3".ctid
+ Filter: ("T 3".c1 = 50)
+ (24 rows)
+
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT t1.c1, t2.c1, t3.c1 FROM (SELECT c1 FROM "S 1"."T 3" WHERE c1 = 50) t1
INNER JOIN (ft4 t2 FULL JOIN ft5 t3 ON (t2.c1::bit(16) = t3.c1::bit(16))) ON
(TRUE) ORDER BY t1.c1, t2.c1, t3.c1 FOR UPDATE OF t1;
+
QUERY PLAN
+
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ LockRows
+ Output: "T 3".c1, t2.c1, t3.c1, "T 3".ctid, t2.*, t3.*
+ -> Sort
+ Output: "T 3".c1, t2.c1, t3.c1, "T 3".ctid, t2.*, t3.*
+ Sort Key: t2.c1, t3.c1
+ -> Nested Loop
+ Output: "T 3".c1, t2.c1, t3.c1, "T 3".ctid, t2.*, t3.*
+ -> Seq Scan on "S 1"."T 3"
+ Output: "T 3".c1, "T 3".ctid
+ Filter: ("T 3".c1 = 50)
+ -> Foreign Scan
+ Output: t2.c1, t2.*, t3.c1, t3.*
+ Relations: (public.ft4 t2) FULL JOIN (public.ft5 t3)
+ Remote SQL: SELECT r2.c1, CASE WHEN (r2.*)::text IS NOT
NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, r3.c1, CASE WHEN (r3.*)::text IS NOT
NULL THEN ROW(r3.c1, r3.c2, r3.c3) END FROM ("S 1"."T 3" r2 FULL JOIN "S 1"."T
4" r3 ON (((r2.c1::bit(16) = r3.c1::bit(16)))))
+ -> Merge Full Join
+ Output: t2.c1, t2.*, t3.c1, t3.*
+ Merge Cond: (((t2.c1)::bit(16)) =
((t3.c1)::bit(16)))
+ -> Sort
+ Output: t2.c1, t2.*, ((t2.c1)::bit(16))
+ Sort Key: ((t2.c1)::bit(16))
+ -> Foreign Scan on public.ft4 t2
+ Output: t2.c1, t2.*, (t2.c1)::bit(16)
+ Remote SQL: SELECT c1, c2, c3 FROM "S
1"."T 3"
+ -> Sort
+ Output: t3.c1, t3.*, ((t3.c1)::bit(16))
+ Sort Key: ((t3.c1)::bit(16))
+ -> Foreign Scan on public.ft5 t3
+ Output: t3.c1, t3.*, (t3.c1)::bit(16)
+ Remote SQL: SELECT c1, c2, c3 FROM "S
1"."T 4"
+ (29 rows)
+
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT t1.c1, t2.c1, t3.c1 FROM (SELECT c1 FROM "S 1"."T 3" WHERE c1 = 50) t1
INNER JOIN (ft4 t2 FULL JOIN ft5 t3 ON (FALSE)) ON (TRUE) ORDER BY t1.c1,
t2.c1, t3.c1 FOR UPDATE OF t1;
+
QUERY PLAN
+
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ LockRows
+ Output: "T 3".c1, t2.c1, t3.c1, "T 3".ctid, t2.*, t3.*
+ -> Nested Loop
+ Output: "T 3".c1, t2.c1, t3.c1, "T 3".ctid, t2.*, t3.*
+ -> Foreign Scan
+ Output: t2.c1, t2.*, t3.c1, t3.*
+ Relations: (public.ft4 t2) FULL JOIN (public.ft5 t3)
+ Remote SQL: SELECT r2.c1, CASE WHEN (r2.*)::text IS NOT NULL
THEN ROW(r2.c1, r2.c2, r2.c3) END, r3.c1, CASE WHEN (r3.*)::text IS NOT NULL
THEN ROW(r3.c1, r3.c2, r3.c3) END FROM ("S 1"."T 3" r2 FULL JOIN "S 1"."T 4" r3
ON ((false))) ORDER BY r2.c1 ASC NULLS LAST, r3.c1 ASC NULLS LAST
+ -> Merge Full Join
+ Output: t2.c1, t2.*, t3.c1, t3.*
+ Join Filter: false
+ -> Foreign Scan on public.ft4 t2
+ Output: t2.c1, t2.*
+ Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
+ -> Materialize
+ Output: t3.c1, t3.*
+ -> Foreign Scan on public.ft5 t3
+ Output: t3.c1, t3.*
+ Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T
4"
+ -> Materialize
+ Output: "T 3".c1, "T 3".ctid
+ -> Seq Scan on "S 1"."T 3"
+ Output: "T 3".c1, "T 3".ctid
+ Filter: ("T 3".c1 = 50)
+ (24 rows)
+
-- join in CTE
EXPLAIN (VERBOSE, COSTS OFF)
WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2
t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100
LIMIT 10;
***************
*** 4298,4315 **** UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9',
c7 = DEFAULT
Output: ft2.c1, (ft2.c2 + 500), NULL::integer, (ft2.c3 ||
'_update9'::text), ft2.c4, ft2.c5, ft2.c6, 'ft2 '::character(10), ft2.c8,
ft2.ctid, ft1.*
Relations: (public.ft2) INNER JOIN (public.ft1)
Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6,
r1.c8, r1.ctid, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2,
r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN
"S 1"."T 1" r2 ON (((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 9)))) FOR
UPDATE OF r1
! -> Hash Join
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6,
ft2.c8, ft2.ctid, ft1.*
! Hash Cond: (ft2.c2 = ft1.c1)
-> Foreign Scan on public.ft2
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6,
ft2.c8, ft2.ctid
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c8, ctid
FROM "S 1"."T 1" FOR UPDATE
! -> Hash
Output: ft1.*, ft1.c1
! -> Foreign Scan on public.ft1
! Output: ft1.*, ft1.c1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7,
c8 FROM "S 1"."T 1" WHERE ((("C 1" % 10) = 9))
! (17 rows)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
--- 4370,4385 ----
Output: ft2.c1, (ft2.c2 + 500), NULL::integer, (ft2.c3 ||
'_update9'::text), ft2.c4, ft2.c5, ft2.c6, 'ft2 '::character(10), ft2.c8,
ft2.ctid, ft1.*
Relations: (public.ft2) INNER JOIN (public.ft1)
Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6,
r1.c8, r1.ctid, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2,
r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN
"S 1"."T 1" r2 ON (((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 9)))) FOR
UPDATE OF r1
! -> Nested Loop
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6,
ft2.c8, ft2.ctid, ft1.*
! Join Filter: (ft2.c2 = ft1.c1)
-> Foreign Scan on public.ft2
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6,
ft2.c8, ft2.ctid
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c8, ctid
FROM "S 1"."T 1" FOR UPDATE
! -> Foreign Scan on public.ft1
Output: ft1.*, ft1.c1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8
FROM "S 1"."T 1" WHERE ((("C 1" % 10) = 9))
! (15 rows)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
***************
*** 4441,4458 **** DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 %
10 = 2;
Output: ft2.ctid, ft1.*
Relations: (public.ft2) INNER JOIN (public.ft1)
Remote SQL: SELECT r1.ctid, CASE WHEN (r2.*)::text IS NOT NULL THEN
ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S
1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1.c2 = r2."C 1")) AND (((r2."C 1"
% 10) = 2)))) FOR UPDATE OF r1
! -> Hash Join
Output: ft2.ctid, ft1.*
! Hash Cond: (ft2.c2 = ft1.c1)
-> Foreign Scan on public.ft2
Output: ft2.ctid, ft2.c2
Remote SQL: SELECT c2, ctid FROM "S 1"."T 1" FOR UPDATE
! -> Hash
Output: ft1.*, ft1.c1
! -> Foreign Scan on public.ft1
! Output: ft1.*, ft1.c1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7,
c8 FROM "S 1"."T 1" WHERE ((("C 1" % 10) = 2))
! (17 rows)
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
--- 4511,4526 ----
Output: ft2.ctid, ft1.*
Relations: (public.ft2) INNER JOIN (public.ft1)
Remote SQL: SELECT r1.ctid, CASE WHEN (r2.*)::text IS NOT NULL THEN
ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S
1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1.c2 = r2."C 1")) AND (((r2."C 1"
% 10) = 2)))) FOR UPDATE OF r1
! -> Nested Loop
Output: ft2.ctid, ft1.*
! Join Filter: (ft2.c2 = ft1.c1)
-> Foreign Scan on public.ft2
Output: ft2.ctid, ft2.c2
Remote SQL: SELECT c2, ctid FROM "S 1"."T 1" FOR UPDATE
! -> Foreign Scan on public.ft1
Output: ft1.*, ft1.c1
! Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8
FROM "S 1"."T 1" WHERE ((("C 1" % 10) = 2))
! (15 rows)
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
***************
*** 4494,4514 **** postgresGetForeignJoinPaths(PlannerInfo *root,
/*
* If there is a possibility that EvalPlanQual will be executed, we need
* to be able to reconstruct the row using scans of the base relations.
! * GetExistingLocalJoinPath will find a suitable path for this purpose
in
! * the path list of the joinrel, if one exists. We must be careful to
! * call it before adding any ForeignPath, since the ForeignPath might
! * dominate the only suitable local path available. We also do it
before
! * calling foreign_join_ok(), since that function updates fpinfo and
marks
! * it as pushable if the join is found to be pushable.
*/
if (root->parse->commandType == CMD_DELETE ||
root->parse->commandType == CMD_UPDATE ||
root->rowMarks)
{
! epq_path = GetExistingLocalJoinPath(joinrel);
if (!epq_path)
{
! elog(DEBUG3, "could not push down foreign join because
a local path suitable for EPQ checks was not found");
return;
}
}
--- 4494,4514 ----
/*
* If there is a possibility that EvalPlanQual will be executed, we need
* to be able to reconstruct the row using scans of the base relations.
! * CreateLocalJoinPath will build an alternative local join path for
this
! * purpose. We must be careful to call it before calling
foreign_join_ok,
! * since that function updates fpinfo and marks it as pushable if the
join
! * is found to be pushable.
*/
if (root->parse->commandType == CMD_DELETE ||
root->parse->commandType == CMD_UPDATE ||
root->rowMarks)
{
! /* Create an unparameterized local join path */
! epq_path = CreateLocalJoinPath(root, joinrel, outerrel,
innerrel,
!
jointype, extra);
if (!epq_path)
{
! elog(DEBUG3, "could not push down foreign join because
a local path suitable for EPQ checks could not be created");
return;
}
}
***************
*** 4517,4523 **** postgresGetForeignJoinPaths(PlannerInfo *root,
if (!foreign_join_ok(root, joinrel, jointype, outerrel, innerrel,
extra))
{
! /* Free path required for EPQ if we copied one; we don't need
it now */
if (epq_path)
pfree(epq_path);
return;
--- 4517,4523 ----
if (!foreign_join_ok(root, joinrel, jointype, outerrel, innerrel,
extra))
{
! /* Free path required for EPQ if we created one; we don't need
it now */
if (epq_path)
pfree(epq_path);
return;
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 493,498 **** SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)
ORDER BY t1.c3, t
--- 493,505 ----
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY
t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE;
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY
t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE;
+ -- FOR UPDATE/SHARE in situations where a full join is pushed down
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT t1.c1, t2.c1, t3.c1 FROM (SELECT c1 FROM "S 1"."T 3" WHERE c1 = 50) t1
INNER JOIN (ft4 t2 FULL JOIN ft5 t3 ON (t2.c1 = t3.c1)) ON (TRUE) ORDER BY
t1.c1, t2.c1, t3.c1 FOR UPDATE OF t1;
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT t1.c1, t2.c1, t3.c1 FROM (SELECT c1 FROM "S 1"."T 3" WHERE c1 = 50) t1
INNER JOIN (ft4 t2 FULL JOIN ft5 t3 ON (t2.c1::bit(16) = t3.c1::bit(16))) ON
(TRUE) ORDER BY t1.c1, t2.c1, t3.c1 FOR UPDATE OF t1;
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT t1.c1, t2.c1, t3.c1 FROM (SELECT c1 FROM "S 1"."T 3" WHERE c1 = 50) t1
INNER JOIN (ft4 t2 FULL JOIN ft5 t3 ON (FALSE)) ON (TRUE) ORDER BY t1.c1,
t2.c1, t3.c1 FOR UPDATE OF t1;
-- join in CTE
EXPLAIN (VERBOSE, COSTS OFF)
WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2
t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100
LIMIT 10;
*** a/doc/src/sgml/fdwhandler.sgml
--- b/doc/src/sgml/fdwhandler.sgml
***************
*** 995,1005 **** RecheckForeignScan (ForeignScanState *node, TupleTableSlot
*slot);
can be executed and the resulting tuple can be stored in the slot.
This plan need not be efficient since no base table will return more
than one row; for example, it may implement all joins as nested loops.
! The function <literal>GetExistingLocalJoinPath</> may be used to search
! existing paths for a suitable local join path, which can be used as the
! alternative local join plan. <literal>GetExistingLocalJoinPath</>
! searches for an unparameterized path in the path list of the specified
! join relation. (If it does not find such a path, it returns NULL, in
which case a foreign data wrapper may build the local path by itself or
may choose not to create access paths for that join.)
</para>
--- 995,1003 ----
can be executed and the resulting tuple can be stored in the slot.
This plan need not be efficient since no base table will return more
than one row; for example, it may implement all joins as nested loops.
! The function <literal>CreateLocalJoinPath</> may be used to build
! a suitable local join path, which can be used to create an alternative
! local join plan. (If it does not build such a path, it returns NULL, in
which case a foreign data wrapper may build the local path by itself or
may choose not to create access paths for that join.)
</para>
*** a/src/backend/foreign/foreign.c
--- b/src/backend/foreign/foreign.c
***************
*** 22,27 ****
--- 22,30 ----
#include "foreign/foreign.h"
#include "lib/stringinfo.h"
#include "miscadmin.h"
+ #include "optimizer/cost.h"
+ #include "optimizer/pathnode.h"
+ #include "optimizer/paths.h"
#include "utils/builtins.h"
#include "utils/memutils.h"
#include "utils/rel.h"
***************
*** 689,801 **** get_foreign_server_oid(const char *servername, bool missing_ok)
return oid;
}
/*
! * Get a copy of an existing local path for a given join relation.
! *
! * This function is usually helpful to obtain an alternate local path for EPQ
! * checks.
! *
! * Right now, this function only supports unparameterized foreign joins, so we
! * only search for unparameterized path in the given list of paths. Since we
! * are searching for a path which can be used to construct an alternative
local
! * plan for a foreign join, we look for only MergeJoin, HashJoin or NestLoop
! * paths.
! *
! * If the inner or outer subpath of the chosen path is a ForeignScan, we
! * replace it with its outer subpath. For this reason, and also because the
! * planner might free the original path later, the path returned by this
! * function is a shallow copy of the original. There's no need to copy
! * the substructure, so we don't.
*
! * Since the plan created using this path will presumably only be used to
! * execute EPQ checks, efficiency of the path is not a concern. But since the
! * path list in RelOptInfo is anyway sorted by total cost we are likely to
! * choose the most efficient path, which is all for the best.
*/
! extern Path *
! GetExistingLocalJoinPath(RelOptInfo *joinrel)
{
! ListCell *lc;
! Assert(IS_JOIN_REL(joinrel));
! foreach(lc, joinrel->pathlist)
{
! Path *path = (Path *) lfirst(lc);
! JoinPath *joinpath = NULL;
! /* Skip parameterized paths. */
! if (path->param_info != NULL)
! continue;
! switch (path->pathtype)
! {
! case T_HashJoin:
{
! HashPath *hash_path =
makeNode(HashPath);
!
! memcpy(hash_path, path,
sizeof(HashPath));
! joinpath = (JoinPath *) hash_path;
}
! break;
!
! case T_NestLoop:
{
! NestPath *nest_path =
makeNode(NestPath);
!
! memcpy(nest_path, path,
sizeof(NestPath));
! joinpath = (JoinPath *) nest_path;
! }
! break;
! case T_MergeJoin:
! {
! MergePath *merge_path =
makeNode(MergePath);
! memcpy(merge_path, path,
sizeof(MergePath));
! joinpath = (JoinPath *) merge_path;
}
! break;
!
! default:
!
! /*
! * Just skip anything else. We don't know if
corresponding
! * plan would build the output row from
whole-row references
! * of base relations and execute the EPQ checks.
! */
! break;
! }
!
! /* This path isn't good for us, check next. */
! if (!joinpath)
! continue;
!
! /*
! * If either inner or outer path is a ForeignPath corresponding
to a
! * pushed down join, replace it with the fdw_outerpath, so that
we
! * maintain path for EPQ checks built entirely of local join
! * strategies.
! */
! if (IsA(joinpath->outerjoinpath, ForeignPath))
! {
! ForeignPath *foreign_path;
!
! foreign_path = (ForeignPath *) joinpath->outerjoinpath;
! if (IS_JOIN_REL(foreign_path->path.parent))
! joinpath->outerjoinpath =
foreign_path->fdw_outerpath;
! }
!
! if (IsA(joinpath->innerjoinpath, ForeignPath))
! {
! ForeignPath *foreign_path;
!
! foreign_path = (ForeignPath *) joinpath->innerjoinpath;
! if (IS_JOIN_REL(foreign_path->path.parent))
! joinpath->innerjoinpath =
foreign_path->fdw_outerpath;
! }
!
! return (Path *) joinpath;
}
! return NULL;
}
--- 692,874 ----
return oid;
}
+
/*
! * Build an alternative local join path to reconstruct a join tuple for the
! * foreign-join in EvalPlanQual
*
! * 'joinrel' is the join relation
! * 'outerrel' is the outer join relation
! * 'innerrel' is the inner join relation
! * 'jointype' is the type of join to do
! * 'extra' contains additional input values
*/
! Path *
! CreateLocalJoinPath(PlannerInfo *root,
! RelOptInfo *joinrel,
! RelOptInfo *outerrel,
! RelOptInfo *innerrel,
! JoinType jointype,
! JoinPathExtraData *extra)
{
! Path *result;
! Path *outer_path = outerrel->cheapest_total_path;
! Path *inner_path = innerrel->cheapest_total_path;
! /*
! * We don't try to build the alternative local join path if the cheapest
! * total outer and inner paths are parameterized; that seems unlikely in
! * normal case.
! */
! if (outer_path->param_info != NULL || inner_path->param_info != NULL)
! return NULL;
! switch (jointype)
{
! case JOIN_INNER:
! case JOIN_LEFT:
! case JOIN_SEMI:
! case JOIN_ANTI:
! {
! JoinCostWorkspace workspace;
! /* Get an initial estimate */
! initial_cost_nestloop(root, &workspace,
jointype,
!
outer_path, inner_path, extra);
! /* Generate a nestloop path */
! result = (Path *) create_nestloop_path(root,
!
joinrel,
!
jointype,
!
&workspace,
!
extra,
!
outer_path,
!
inner_path,
!
extra->restrictlist,
!
NIL,
!
NULL);
! }
! break;
! case JOIN_RIGHT:
! case JOIN_FULL:
! {
! JoinCostWorkspace workspace;
! /* Generate a hashjoin or mergejoin path, if
possible */
! if (extra->hashclause_list)
{
! /* Get an initial estimate */
! initial_cost_hashjoin(root, &workspace,
jointype,
!
extra->hashclause_list,
!
outer_path, inner_path, extra);
! /* Generate a hashjoin path */
! result = (Path *)
create_hashjoin_path(root,
!
joinrel,
!
jointype,
!
&workspace,
!
extra,
!
outer_path,
!
inner_path,
!
extra->restrictlist,
!
NULL,
!
extra->hashclause_list);
}
! else if (extra->mergejoin_allowed)
{
! /*
! * If special case: for "x FULL JOIN y
ON true" or "x FULL
! * JOIN y ON false", there will be no
join clauses at all;
! * create a clauseless mergejoin path.
Else create a
! * mergejoin path by explicitly sorting
both the outer and
! * inner relations.
! */
! if (!extra->mergeclause_list)
! {
! /* Get an initial estimate */
! initial_cost_mergejoin(root,
&workspace, jointype,
!
NIL, outer_path, inner_path,
!
NIL, NIL, extra);
! /* Generate a mergejoin path */
! result = (Path *)
create_mergejoin_path(root,
!
joinrel,
!
jointype,
!
&workspace,
!
extra,
!
outer_path,
!
inner_path,
!
extra->restrictlist,
!
NIL,
!
NULL,
!
NIL,
!
NIL,
!
NIL);
! }
! else
! {
! List *mergeclauses;
! List *outerkeys;
! List *innerkeys;
! /* Build sort pathkeys for the
outer side */
! outerkeys =
select_outer_pathkeys_for_merge(root,
!
extra->mergeclause_list,
!
joinrel);
! /* Sort the mergeclauses into
the corresponding ordering */
! mergeclauses =
find_mergeclauses_for_pathkeys(root,
!
outerkeys,
!
true,
!
extra->mergeclause_list);
! /* Should have used them all...
*/
!
Assert(list_length(mergeclauses) == list_length(extra->mergeclause_list));
! /* Build sort pathkeys for the
inner side */
! innerkeys =
make_inner_pathkeys_for_merge(root,
!
mergeclauses,
!
outerkeys);
! /*
! * It's possible that the
cheapest total paths will
! * already be sorted properly;
if so, suppress an
! * explicit sort.
! */
! if (outerkeys &&
!
pathkeys_contained_in(outerkeys,
!
outer_path->pathkeys))
! outerkeys = NIL;
! if (innerkeys &&
!
pathkeys_contained_in(innerkeys,
!
inner_path->pathkeys))
! innerkeys = NIL;
! /* Get an initial estimate */
! initial_cost_mergejoin(root,
&workspace, jointype,
!
mergeclauses,
!
outer_path, inner_path,
!
outerkeys, innerkeys, extra);
! /* Generate a mergejoin path */
! result = (Path *)
create_mergejoin_path(root,
!
joinrel,
!
jointype,
!
&workspace,
!
extra,
!
outer_path,
!
inner_path,
!
extra->restrictlist,
!
NIL,
!
NULL,
!
mergeclauses,
!
outerkeys,
!
innerkeys);
! }
}
! else
! result = NULL;
! }
! break;
! default:
! /* other values not expected here */
! elog(ERROR, "unrecognized join type: %d",
! (int) jointype);
! result = NULL; /* keep compiler quiet */
! break;
}
!
! return result;
}
*** a/src/backend/optimizer/path/joinpath.c
--- b/src/backend/optimizer/path/joinpath.c
***************
*** 68,73 **** static List *select_mergejoin_clauses(PlannerInfo *root,
--- 68,77 ----
List *restrictlist,
JoinType jointype,
bool *mergejoin_allowed);
+ static List *select_hashjoin_clauses(RelOptInfo *outerrel,
+ RelOptInfo *innerrel,
+ List *restrictlist,
+ JoinType jointype);
static void generate_mergejoin_paths(PlannerInfo *root,
RelOptInfo *joinrel,
RelOptInfo *innerrel,
***************
*** 113,123 **** add_paths_to_joinrel(PlannerInfo *root,
List *restrictlist)
{
JoinPathExtraData extra;
- bool mergejoin_allowed = true;
ListCell *lc;
extra.restrictlist = restrictlist;
extra.mergeclause_list = NIL;
extra.sjinfo = sjinfo;
extra.param_source_rels = NULL;
--- 117,128 ----
List *restrictlist)
{
JoinPathExtraData extra;
ListCell *lc;
extra.restrictlist = restrictlist;
extra.mergeclause_list = NIL;
+ extra.mergejoin_allowed = true;
+ extra.hashclause_list = NIL;
extra.sjinfo = sjinfo;
extra.param_source_rels = NULL;
***************
*** 177,183 **** add_paths_to_joinrel(PlannerInfo *root,
innerrel,
restrictlist,
jointype,
!
&mergejoin_allowed);
/*
* If it's SEMI, ANTI, or inner_unique join, compute correction factors
--- 182,203 ----
innerrel,
restrictlist,
jointype,
!
&extra.mergejoin_allowed);
!
! /*
! * Find hashjoin clauses. As above, we can skip this if we are not
! * interested in doing a hashjoin, but disregard enable_hashjoin for
full
! * joins, because there may be no other alternative.
! *
! * Note: we need to build only one hashclause list for any given pair of
! * outer and inner relations; all of the hashable clauses will be used
as
! * keys.
! */
! if (enable_hashjoin || jointype == JOIN_FULL)
! extra.hashclause_list = select_hashjoin_clauses(outerrel,
!
innerrel,
!
restrictlist,
!
jointype);
/*
* If it's SEMI, ANTI, or inner_unique join, compute correction factors
***************
*** 240,246 **** add_paths_to_joinrel(PlannerInfo *root,
* 1. Consider mergejoin paths where both relations must be explicitly
* sorted. Skip this if we can't mergejoin.
*/
! if (mergejoin_allowed)
sort_inner_and_outer(root, joinrel, outerrel, innerrel,
jointype, &extra);
--- 260,266 ----
* 1. Consider mergejoin paths where both relations must be explicitly
* sorted. Skip this if we can't mergejoin.
*/
! if (extra.mergejoin_allowed)
sort_inner_and_outer(root, joinrel, outerrel, innerrel,
jointype, &extra);
***************
*** 251,257 **** add_paths_to_joinrel(PlannerInfo *root,
* (That's okay because we know that nestloop can't handle right/full
* joins at all, so it wouldn't work in the prohibited cases either.)
*/
! if (mergejoin_allowed)
match_unsorted_outer(root, joinrel, outerrel, innerrel,
jointype, &extra);
--- 271,277 ----
* (That's okay because we know that nestloop can't handle right/full
* joins at all, so it wouldn't work in the prohibited cases either.)
*/
! if (extra.mergejoin_allowed)
match_unsorted_outer(root, joinrel, outerrel, innerrel,
jointype, &extra);
***************
*** 268,274 **** add_paths_to_joinrel(PlannerInfo *root,
* those made by match_unsorted_outer when add_paths_to_joinrel() is
* invoked with the two rels given in the other order.
*/
! if (mergejoin_allowed)
match_unsorted_inner(root, joinrel, outerrel, innerrel,
jointype, &extra);
#endif
--- 288,294 ----
* those made by match_unsorted_outer when add_paths_to_joinrel() is
* invoked with the two rels given in the other order.
*/
! if (extra.mergejoin_allowed)
match_unsorted_inner(root, joinrel, outerrel, innerrel,
jointype, &extra);
#endif
***************
*** 1589,1629 **** hash_inner_and_outer(PlannerInfo *root,
JoinPathExtraData *extra)
{
JoinType save_jointype = jointype;
! bool isouterjoin = IS_OUTER_JOIN(jointype);
! List *hashclauses;
! ListCell *l;
!
! /*
! * We need to build only one hashclauses list for any given pair of
outer
! * and inner relations; all of the hashable clauses will be used as
keys.
! *
! * Scan the join's restrictinfo list to find hashjoinable clauses that
are
! * usable with this pair of sub-relations.
! */
! hashclauses = NIL;
! foreach(l, extra->restrictlist)
! {
! RestrictInfo *restrictinfo = (RestrictInfo *) lfirst(l);
!
! /*
! * If processing an outer join, only use its own join clauses
for
! * hashing. For inner joins we need not be so picky.
! */
! if (isouterjoin && restrictinfo->is_pushed_down)
! continue;
!
! if (!restrictinfo->can_join ||
! restrictinfo->hashjoinoperator == InvalidOid)
! continue; /* not hashjoinable */
!
! /*
! * Check if clause has the form "outer op inner" or "inner op
outer".
! */
! if (!clause_sides_match_join(restrictinfo, outerrel, innerrel))
! continue; /* no good for these
input relations */
!
! hashclauses = lappend(hashclauses, restrictinfo);
! }
/* If we found any usable hashclauses, make paths */
if (hashclauses)
--- 1609,1615 ----
JoinPathExtraData *extra)
{
JoinType save_jointype = jointype;
! List *hashclauses = extra->hashclause_list;
/* If we found any usable hashclauses, make paths */
if (hashclauses)
***************
*** 1910,1912 **** select_mergejoin_clauses(PlannerInfo *root,
--- 1896,1949 ----
return result_list;
}
+
+ /*
+ * select_hashjoin_clauses
+ * Select hashjoin clauses that are usable for a particular join.
+ * Returns a list of RestrictInfo nodes for those clauses.
+ *
+ * As in select_mergejoin_clauses(), we mark each selected RestrictInfo to
+ * show which side is currently being considered as outer. These are
+ * transient markings that are only good for the duration of the current
+ * add_paths_to_joinrel() call!
+ *
+ * We examine each restrictinfo clause known for the join to see
+ * if it is hashjoinable and involves vars from the two sub-relations
+ * currently of interest.
+ */
+ static List *
+ select_hashjoin_clauses(RelOptInfo *outerrel,
+ RelOptInfo *innerrel,
+ List *restrictlist,
+ JoinType jointype)
+ {
+ List *hashclauses = NIL;
+ bool isouterjoin = IS_OUTER_JOIN(jointype);
+ ListCell *l;
+
+ foreach(l, restrictlist)
+ {
+ RestrictInfo *restrictinfo = (RestrictInfo *) lfirst(l);
+
+ /*
+ * If processing an outer join, only use its own join clauses
for
+ * hashing. For inner joins we need not be so picky.
+ */
+ if (isouterjoin && restrictinfo->is_pushed_down)
+ continue;
+
+ if (!restrictinfo->can_join ||
+ restrictinfo->hashjoinoperator == InvalidOid)
+ continue; /* not hashjoinable */
+
+ /*
+ * Check if clause has the form "outer op inner" or "inner op
outer".
+ */
+ if (!clause_sides_match_join(restrictinfo, outerrel, innerrel))
+ continue; /* no good for these
input relations */
+
+ hashclauses = lappend(hashclauses, restrictinfo);
+ }
+
+ return hashclauses;
+ }
*** a/src/include/foreign/fdwapi.h
--- b/src/include/foreign/fdwapi.h
***************
*** 237,242 **** extern FdwRoutine *GetFdwRoutineByRelId(Oid relid);
extern FdwRoutine *GetFdwRoutineForRelation(Relation relation, bool makecopy);
extern bool IsImportableForeignTable(const char *tablename,
ImportForeignSchemaStmt *stmt);
! extern Path *GetExistingLocalJoinPath(RelOptInfo *joinrel);
#endif /* FDWAPI_H */
--- 237,247 ----
extern FdwRoutine *GetFdwRoutineForRelation(Relation relation, bool makecopy);
extern bool IsImportableForeignTable(const char *tablename,
ImportForeignSchemaStmt *stmt);
! extern Path *CreateLocalJoinPath(PlannerInfo *root,
! RelOptInfo *joinrel,
! RelOptInfo *outerrel,
! RelOptInfo *innerrel,
! JoinType jointype,
! JoinPathExtraData *extra);
#endif /* FDWAPI_H */
*** a/src/include/nodes/relation.h
--- b/src/include/nodes/relation.h
***************
*** 2171,2176 **** typedef struct SemiAntiJoinFactors
--- 2171,2179 ----
* clauses that apply to this join
* mergeclause_list is a list of RestrictInfo nodes for available
* mergejoin clauses in this join
+ * mergejoin_allowed is a flag to indicate whether mergejoins are allowed
+ * hashclause_list is a list of RestrictInfo nodes for available
+ * hashjoin clauses in this join
* inner_unique is true if each outer tuple provably matches no more
* than one inner tuple
* sjinfo is extra info about special joins for selectivity estimation
***************
*** 2181,2186 **** typedef struct JoinPathExtraData
--- 2184,2191 ----
{
List *restrictlist;
List *mergeclause_list;
+ bool mergejoin_allowed;
+ List *hashclause_list;
bool inner_unique;
SpecialJoinInfo *sjinfo;
SemiAntiJoinFactors semifactors;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
