From e0c21e620804adbdeb6bc7d11ad606d263e63249 Mon Sep 17 00:00:00 2001 From: Alexander Pyhalov Date: Mon, 24 Jan 2022 18:28:12 +0300 Subject: [PATCH] Look through all possible foreign join orders --- .../postgres_fdw/expected/postgres_fdw.out | 10 ++-- contrib/postgres_fdw/postgres_fdw.c | 47 ++++++++++++++++--- 2 files changed, 46 insertions(+), 11 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 7d6f7d9e3df..1547049108c 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -1203,8 +1203,8 @@ SELECT t1.c1, t2.c2, t3.c3 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) JOIN ft4 t --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: t1.c1, t2.c2, t3.c3, t1.c3 - Relations: ((public.ft1 t1) INNER JOIN (public.ft2 t2)) INNER JOIN (public.ft4 t3) - Remote SQL: SELECT r1."C 1", r2.c2, r4.c3, r1.c3 FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) INNER JOIN "S 1"."T 3" r4 ON (((r1."C 1" = r4.c1)))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST LIMIT 10::bigint OFFSET 10::bigint + Relations: ((public.ft1 t1) INNER JOIN (public.ft4 t3)) INNER JOIN (public.ft2 t2) + Remote SQL: SELECT r1."C 1", r2.c2, r4.c3, r1.c3 FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r4 ON (((r1."C 1" = r4.c1)))) 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 LIMIT 10::bigint OFFSET 10::bigint (4 rows) SELECT t1.c1, t2.c2, t3.c3 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) JOIN ft4 t3 ON (t3.c1 = t1.c1) ORDER BY t1.c3, t1.c1 OFFSET 10 LIMIT 10; @@ -5585,12 +5585,12 @@ UPDATE ft2 SET c3 = 'foo' FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1) WHERE ft2.c1 > 1200 AND ft2.c2 = ft4.c1 RETURNING ft2, ft2.*, ft4, ft4.*; -- can be pushed down - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Update on public.ft2 Output: ft2.*, ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.*, ft4.c1, ft4.c2, ft4.c3 -> Foreign Update - Remote SQL: UPDATE "S 1"."T 1" r1 SET c3 = 'foo'::text FROM ("S 1"."T 3" r2 INNER JOIN "S 1"."T 4" r3 ON (TRUE)) WHERE ((r2.c1 = r3.c1)) AND ((r1.c2 = r2.c1)) AND ((r1."C 1" > 1200)) RETURNING r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, r2.c1, r2.c2, r2.c3 + Remote SQL: UPDATE "S 1"."T 1" r1 SET c3 = 'foo'::text FROM ("S 1"."T 3" r2 INNER JOIN "S 1"."T 4" r3 ON (((r2.c1 = r3.c1)))) WHERE ((r2.c1 = r1.c2)) AND ((r1."C 1" > 1200)) RETURNING r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, r2.c1, r2.c2, r2.c3 (4 rows) UPDATE ft2 SET c3 = 'foo' diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index bf3f3d9e26e..613d3c8a10d 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -5950,7 +5950,7 @@ postgresGetForeignJoinPaths(PlannerInfo *root, JoinType jointype, JoinPathExtraData *extra) { - PgFdwRelationInfo *fpinfo; + PgFdwRelationInfo *fpinfo, *oldfpinfo; ForeignPath *joinpath; double rows; int width; @@ -5960,9 +5960,10 @@ postgresGetForeignJoinPaths(PlannerInfo *root, * EvalPlanQual gets triggered. */ /* - * Skip if this join combination has been considered already. + * Skip if this join combination has been considered already and rejected. */ - if (joinrel->fdw_private) + oldfpinfo = (PgFdwRelationInfo *) joinrel->fdw_private; + if (oldfpinfo && !oldfpinfo->pushdown_safe) return; /* @@ -6002,6 +6003,12 @@ postgresGetForeignJoinPaths(PlannerInfo *root, epq_path = GetExistingLocalJoinPath(joinrel); if (!epq_path) { + if (oldfpinfo) + { + joinrel->fdw_private = oldfpinfo; + pfree(fpinfo); + } + elog(DEBUG3, "could not push down foreign join because a local path suitable for EPQ checks was not found"); return; } @@ -6011,6 +6018,12 @@ postgresGetForeignJoinPaths(PlannerInfo *root, if (!foreign_join_ok(root, joinrel, jointype, outerrel, innerrel, extra)) { + if (oldfpinfo) + { + joinrel->fdw_private = oldfpinfo; + pfree(fpinfo); + } + /* Free path required for EPQ if we copied one; we don't need it now */ if (epq_path) pfree(epq_path); @@ -6044,14 +6057,36 @@ postgresGetForeignJoinPaths(PlannerInfo *root, /* Estimate costs for bare join relation */ estimate_path_cost_size(root, joinrel, NIL, NIL, NULL, &rows, &width, &startup_cost, &total_cost); - /* Now update this information in the joinrel */ - joinrel->rows = rows; - joinrel->reltarget->width = width; + fpinfo->rows = rows; fpinfo->width = width; fpinfo->startup_cost = startup_cost; fpinfo->total_cost = total_cost; + if (oldfpinfo) + { + /* If this path is not cheaper, ignore it */ + if (startup_cost >= oldfpinfo->startup_cost && total_cost >= oldfpinfo->total_cost) + { + joinrel->fdw_private = oldfpinfo; + pfree(fpinfo); + return; + } + else + { + /* + * We don't know which path is selected - old or new one, but let's be optimistic. + */ + fpinfo->startup_cost = Min(oldfpinfo->startup_cost, startup_cost); + fpinfo->total_cost = Min(oldfpinfo->total_cost, total_cost); + pfree(oldfpinfo); + } + } + + /* Now update this information in the joinrel */ + joinrel->rows = rows; + joinrel->reltarget->width = width; + /* * Create a new join path and add it to the joinrel which represents a * join between foreign tables. -- 2.25.1