Skip to content

Commit 9e9931d

Browse files
author
Etsuro Fujita
committed
Re-allow FDWs and custom scan providers to replace joins with pseudoconstant quals.
This was disabled in commit 6f80a8d due to the lack of support for handling of pseudoconstant quals assigned to replaced joins in createplan.c. To re-allow it, this patch adds the support by 1) modifying the ForeignPath and CustomPath structs so that if they represent foreign and custom scans replacing a join with a scan, they store the list of RestrictInfo nodes to apply to the join, as in JoinPaths, and by 2) modifying create_scan_plan() in createplan.c so that it uses that list in that case, instead of the baserestrictinfo list, to get pseudoconstant quals assigned to the join, as mentioned in the commit message for that commit. Important item for the release notes: this is non-backwards-compatible since it modifies the ForeignPath and CustomPath structs, as mentioned above, and changes the argument lists for FDW helper functions create_foreignscan_path(), create_foreign_join_path(), and create_foreign_upper_path(). Richard Guo, with some additional changes by me, reviewed by Nishant Sharma, Suraj Kharage, and Richard Guo. Discussion: https://postgr.es/m/CADrsxdbcN1vejBaf8a%2BQhrZY5PXL-04mCd4GDu6qm6FigDZd6Q%40mail.gmail.com
1 parent 5ffb7c7 commit 9e9931d

File tree

13 files changed

+99
-76
lines changed

13 files changed

+99
-76
lines changed

‎contrib/file_fdw/file_fdw.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -581,6 +581,7 @@ fileGetForeignPaths(PlannerInfo *root,
581581
NIL, /* no pathkeys */
582582
baserel->lateral_relids,
583583
NULL, /* no extra plan */
584+
NIL, /* no fdw_restrictinfo list */
584585
coptions));
585586

586587
/*

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 10 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -2316,31 +2316,21 @@ SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM
23162316
1
23172317
(10 rows)
23182318

2319-
-- join with pseudoconstant quals, not pushed down.
2319+
-- join with pseudoconstant quals
23202320
EXPLAIN (VERBOSE, COSTS OFF)
23212321
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1 AND CURRENT_USER = SESSION_USER) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
2322-
QUERY PLAN
2323-
-------------------------------------------------------------------------------
2322+
QUERY PLAN
2323+
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
23242324
Limit
23252325
Output: t1.c1, t2.c1, t1.c3
2326-
-> Sort
2326+
-> Result
23272327
Output: t1.c1, t2.c1, t1.c3
2328-
Sort Key: t1.c3, t1.c1
2329-
-> Result
2330-
Output: t1.c1, t2.c1, t1.c3
2331-
One-Time Filter: (CURRENT_USER = SESSION_USER)
2332-
-> Hash Join
2333-
Output: t1.c1, t1.c3, t2.c1
2334-
Hash Cond: (t2.c1 = t1.c1)
2335-
-> Foreign Scan on public.ft2 t2
2336-
Output: t2.c1
2337-
Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
2338-
-> Hash
2339-
Output: t1.c1, t1.c3
2340-
-> Foreign Scan on public.ft1 t1
2341-
Output: t1.c1, t1.c3
2342-
Remote SQL: SELECT "C 1", c3 FROM "S 1"."T 1"
2343-
(19 rows)
2328+
One-Time Filter: (CURRENT_USER = SESSION_USER)
2329+
-> Foreign Scan
2330+
Output: t1.c1, t1.c3, t2.c1
2331+
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
2332+
Remote SQL: SELECT r1."C 1", r2."C 1", r1.c3 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r2."C 1" = r1."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST
2333+
(9 rows)
23442334

23452335
-- non-Var items in targetlist of the nullable rel of a join preventing
23462336
-- push-down in some cases

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 15 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -524,7 +524,7 @@ static List *get_useful_pathkeys_for_relation(PlannerInfo *root,
524524
RelOptInfo *rel);
525525
static List *get_useful_ecs_for_relation(PlannerInfo *root, RelOptInfo *rel);
526526
static void add_paths_with_pathkeys_for_rel(PlannerInfo *root, RelOptInfo *rel,
527-
Path *epq_path);
527+
Path *epq_path, List *restrictlist);
528528
static void add_foreign_grouping_paths(PlannerInfo *root,
529529
RelOptInfo *input_rel,
530530
RelOptInfo *grouped_rel,
@@ -1034,11 +1034,12 @@ postgresGetForeignPaths(PlannerInfo *root,
10341034
NIL, /* no pathkeys */
10351035
baserel->lateral_relids,
10361036
NULL, /* no extra plan */
1037+
NIL, /* no fdw_restrictinfo list */
10371038
NIL); /* no fdw_private list */
10381039
add_path(baserel, (Path *) path);
10391040

10401041
/* Add paths with pathkeys */
1041-
add_paths_with_pathkeys_for_rel(root, baserel, NULL);
1042+
add_paths_with_pathkeys_for_rel(root, baserel, NULL, NIL);
10421043

10431044
/*
10441045
* If we're not using remote estimates, stop here. We have no way to
@@ -1206,6 +1207,7 @@ postgresGetForeignPaths(PlannerInfo *root,
12061207
NIL, /* no pathkeys */
12071208
param_info->ppi_req_outer,
12081209
NULL,
1210+
NIL, /* no fdw_restrictinfo list */
12091211
NIL); /* no fdw_private list */
12101212
add_path(baserel, (Path *) path);
12111213
}
@@ -5991,7 +5993,7 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
59915993

59925994
static void
59935995
add_paths_with_pathkeys_for_rel(PlannerInfo *root, RelOptInfo *rel,
5994-
Path *epq_path)
5996+
Path *epq_path, List *restrictlist)
59955997
{
59965998
List *useful_pathkeys_list = NIL; /* List of all pathkeys */
59975999
ListCell *lc;
@@ -6085,6 +6087,7 @@ add_paths_with_pathkeys_for_rel(PlannerInfo *root, RelOptInfo *rel,
60856087
useful_pathkeys,
60866088
rel->lateral_relids,
60876089
sorted_epq_path,
6090+
NIL, /* no fdw_restrictinfo list */
60886091
NIL));
60896092
else
60906093
add_path(rel, (Path *)
@@ -6096,6 +6099,7 @@ add_paths_with_pathkeys_for_rel(PlannerInfo *root, RelOptInfo *rel,
60966099
useful_pathkeys,
60976100
rel->lateral_relids,
60986101
sorted_epq_path,
6102+
restrictlist,
60996103
NIL));
61006104
}
61016105
}
@@ -6348,13 +6352,15 @@ postgresGetForeignJoinPaths(PlannerInfo *root,
63486352
NIL, /* no pathkeys */
63496353
joinrel->lateral_relids,
63506354
epq_path,
6355+
extra->restrictlist,
63516356
NIL); /* no fdw_private */
63526357

63536358
/* Add generated path into joinrel by add_path(). */
63546359
add_path(joinrel, (Path *) joinpath);
63556360

63566361
/* Consider pathkeys for the join relation */
6357-
add_paths_with_pathkeys_for_rel(root, joinrel, epq_path);
6362+
add_paths_with_pathkeys_for_rel(root, joinrel, epq_path,
6363+
extra->restrictlist);
63586364

63596365
/* XXX Consider parameterized paths for the join relation */
63606366
}
@@ -6735,6 +6741,7 @@ add_foreign_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel,
67356741
total_cost,
67366742
NIL, /* no pathkeys */
67376743
NULL,
6744+
NIL, /* no fdw_restrictinfo list */
67386745
NIL); /* no fdw_private */
67396746

67406747
/* Add generated path into grouped_rel by add_path(). */
@@ -6868,6 +6875,7 @@ add_foreign_ordered_paths(PlannerInfo *root, RelOptInfo *input_rel,
68686875
total_cost,
68696876
root->sort_pathkeys,
68706877
NULL, /* no extra plan */
6878+
NIL, /* no fdw_restrictinfo list */
68716879
fdw_private);
68726880

68736881
/* and add it to the ordered_rel */
@@ -6983,7 +6991,8 @@ add_foreign_final_paths(PlannerInfo *root, RelOptInfo *input_rel,
69836991
path->total_cost,
69846992
path->pathkeys,
69856993
NULL, /* no extra plan */
6986-
NULL); /* no fdw_private */
6994+
NIL, /* no fdw_restrictinfo list */
6995+
NIL); /* no fdw_private */
69876996

69886997
/* and add it to the final_rel */
69896998
add_path(final_rel, (Path *) final_path);
@@ -7103,6 +7112,7 @@ add_foreign_final_paths(PlannerInfo *root, RelOptInfo *input_rel,
71037112
total_cost,
71047113
pathkeys,
71057114
NULL, /* no extra plan */
7115+
NIL, /* no fdw_restrictinfo list */
71067116
fdw_private);
71077117

71087118
/* and add it to the final_rel */

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -640,7 +640,7 @@ SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2
640640
EXPLAIN (VERBOSE, COSTS OFF)
641641
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
642642
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
643-
-- join with pseudoconstant quals, not pushed down.
643+
-- join with pseudoconstant quals
644644
EXPLAIN (VERBOSE, COSTS OFF)
645645
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1 AND CURRENT_USER = SESSION_USER) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
646646

‎doc/src/sgml/custom-scan.sgml

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -62,6 +62,7 @@ typedef struct CustomPath
6262
Path path;
6363
uint32 flags;
6464
List *custom_paths;
65+
List *custom_restrictinfo;
6566
List *custom_private;
6667
const CustomPathMethods *methods;
6768
} CustomPath;
@@ -85,6 +86,10 @@ typedef struct CustomPath
8586
An optional <structfield>custom_paths</structfield> is a list of <structname>Path</structname>
8687
nodes used by this custom-path node; these will be transformed into
8788
<structname>Plan</structname> nodes by planner.
89+
As described below, custom paths can be created for join relations as
90+
well. In such a case, <structfield>custom_restrictinfo</structfield>
91+
should be used to store the set of join clauses to apply to the join the
92+
custom path replaces. Otherwise it should be NIL.
8893
<structfield>custom_private</structfield> can be used to store the custom path's
8994
private data. Private data should be stored in a form that can be handled
9095
by <literal>nodeToString</literal>, so that debugging routines that attempt to
@@ -114,6 +119,17 @@ extern PGDLLIMPORT set_join_pathlist_hook_type set_join_pathlist_hook;
114119
responsibility of the hook to minimize duplicated work.
115120
</para>
116121

122+
<para>
123+
Note also that the set of join clauses to apply to the join,
124+
which is passed as <literal>extra-&gt;restrictlist</literal>, varies
125+
depending on the combination of inner and outer relations. A
126+
<structname>CustomPath</structname> path generated for the
127+
<literal>joinrel</literal> must contain the set of join clauses it uses,
128+
which will be used by the planner to convert the
129+
<structname>CustomPath</structname> path into a plan, if it is selected
130+
by the planner as the best path for the <literal>joinrel</literal>.
131+
</para>
132+
117133
<sect2 id="custom-scan-path-callbacks">
118134
<title>Custom Scan Path Callbacks</title>
119135

‎doc/src/sgml/fdwhandler.sgml

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -333,6 +333,17 @@ GetForeignJoinPaths(PlannerInfo *root,
333333
the responsibility of the FDW to minimize duplicated work.
334334
</para>
335335

336+
<para>
337+
Note also that the set of join clauses to apply to the join,
338+
which is passed as <literal>extra-&gt;restrictlist</literal>, varies
339+
depending on the combination of inner and outer relations. A
340+
<structname>ForeignPath</structname> path generated for the
341+
<literal>joinrel</literal> must contain the set of join clauses it uses,
342+
which will be used by the planner to convert the
343+
<structname>ForeignPath</structname> path into a plan, if it is selected
344+
by the planner as the best path for the <literal>joinrel</literal>.
345+
</para>
346+
336347
<para>
337348
If a <structname>ForeignPath</structname> path is chosen for the join, it will
338349
represent the entire join process; paths generated for the component

‎src/backend/optimizer/path/joinpath.c

Lines changed: 2 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -24,7 +24,6 @@
2424
#include "optimizer/pathnode.h"
2525
#include "optimizer/paths.h"
2626
#include "optimizer/planmain.h"
27-
#include "optimizer/restrictinfo.h"
2827
#include "utils/typcache.h"
2928

3029
/* Hook for plugins to get control in add_paths_to_joinrel() */
@@ -131,7 +130,6 @@ add_paths_to_joinrel(PlannerInfo *root,
131130
{
132131
JoinPathExtraData extra;
133132
bool mergejoin_allowed = true;
134-
bool consider_join_pushdown = false;
135133
ListCell *lc;
136134
Relids joinrelids;
137135

@@ -323,34 +321,21 @@ add_paths_to_joinrel(PlannerInfo *root,
323321
hash_inner_and_outer(root, joinrel, outerrel, innerrel,
324322
jointype, &extra);
325323

326-
/*
327-
* createplan.c does not currently support handling of pseudoconstant
328-
* clauses assigned to joins pushed down by extensions; check if the
329-
* restrictlist has such clauses, and if so, disallow pushing down joins.
330-
*/
331-
if ((joinrel->fdwroutine &&
332-
joinrel->fdwroutine->GetForeignJoinPaths) ||
333-
set_join_pathlist_hook)
334-
consider_join_pushdown = !has_pseudoconstant_clauses(root,
335-
restrictlist);
336-
337324
/*
338325
* 5. If inner and outer relations are foreign tables (or joins) belonging
339326
* to the same server and assigned to the same user to check access
340327
* permissions as, give the FDW a chance to push down joins.
341328
*/
342329
if (joinrel->fdwroutine &&
343-
joinrel->fdwroutine->GetForeignJoinPaths &&
344-
consider_join_pushdown)
330+
joinrel->fdwroutine->GetForeignJoinPaths)
345331
joinrel->fdwroutine->GetForeignJoinPaths(root, joinrel,
346332
outerrel, innerrel,
347333
jointype, &extra);
348334

349335
/*
350336
* 6. Finally, give extensions a chance to manipulate the path list.
351337
*/
352-
if (set_join_pathlist_hook &&
353-
consider_join_pushdown)
338+
if (set_join_pathlist_hook)
354339
set_join_pathlist_hook(root, joinrel, outerrel, innerrel,
355340
jointype, &extra);
356341
}

‎src/backend/optimizer/plan/createplan.c

Lines changed: 20 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -599,8 +599,27 @@ create_scan_plan(PlannerInfo *root, Path *best_path, int flags)
599599
* Detect whether we have any pseudoconstant quals to deal with. Then, if
600600
* we'll need a gating Result node, it will be able to project, so there
601601
* are no requirements on the child's tlist.
602+
*
603+
* If this replaces a join, it must be a foreign scan or a custom scan,
604+
* and the FDW or the custom scan provider would have stored in the best
605+
* path the list of RestrictInfo nodes to apply to the join; check against
606+
* that list in that case.
602607
*/
603-
gating_clauses = get_gating_quals(root, scan_clauses);
608+
if (IS_JOIN_REL(rel))
609+
{
610+
List *join_clauses;
611+
612+
Assert(best_path->pathtype == T_ForeignScan ||
613+
best_path->pathtype == T_CustomScan);
614+
if (best_path->pathtype == T_ForeignScan)
615+
join_clauses = ((ForeignPath *) best_path)->fdw_restrictinfo;
616+
else
617+
join_clauses = ((CustomPath *) best_path)->custom_restrictinfo;
618+
619+
gating_clauses = get_gating_quals(root, join_clauses);
620+
}
621+
else
622+
gating_clauses = get_gating_quals(root, scan_clauses);
604623
if (gating_clauses)
605624
flags = 0;
606625

‎src/backend/optimizer/util/pathnode.c

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2229,6 +2229,7 @@ create_foreignscan_path(PlannerInfo *root, RelOptInfo *rel,
22292229
List *pathkeys,
22302230
Relids required_outer,
22312231
Path *fdw_outerpath,
2232+
List *fdw_restrictinfo,
22322233
List *fdw_private)
22332234
{
22342235
ForeignPath *pathnode = makeNode(ForeignPath);
@@ -2250,6 +2251,7 @@ create_foreignscan_path(PlannerInfo *root, RelOptInfo *rel,
22502251
pathnode->path.pathkeys = pathkeys;
22512252

22522253
pathnode->fdw_outerpath = fdw_outerpath;
2254+
pathnode->fdw_restrictinfo = fdw_restrictinfo;
22532255
pathnode->fdw_private = fdw_private;
22542256

22552257
return pathnode;
@@ -2273,6 +2275,7 @@ create_foreign_join_path(PlannerInfo *root, RelOptInfo *rel,
22732275
List *pathkeys,
22742276
Relids required_outer,
22752277
Path *fdw_outerpath,
2278+
List *fdw_restrictinfo,
22762279
List *fdw_private)
22772280
{
22782281
ForeignPath *pathnode = makeNode(ForeignPath);
@@ -2300,6 +2303,7 @@ create_foreign_join_path(PlannerInfo *root, RelOptInfo *rel,
23002303
pathnode->path.pathkeys = pathkeys;
23012304

23022305
pathnode->fdw_outerpath = fdw_outerpath;
2306+
pathnode->fdw_restrictinfo = fdw_restrictinfo;
23032307
pathnode->fdw_private = fdw_private;
23042308

23052309
return pathnode;
@@ -2322,6 +2326,7 @@ create_foreign_upper_path(PlannerInfo *root, RelOptInfo *rel,
23222326
double rows, Cost startup_cost, Cost total_cost,
23232327
List *pathkeys,
23242328
Path *fdw_outerpath,
2329+
List *fdw_restrictinfo,
23252330
List *fdw_private)
23262331
{
23272332
ForeignPath *pathnode = makeNode(ForeignPath);
@@ -2345,6 +2350,7 @@ create_foreign_upper_path(PlannerInfo *root, RelOptInfo *rel,
23452350
pathnode->path.pathkeys = pathkeys;
23462351

23472352
pathnode->fdw_outerpath = fdw_outerpath;
2353+
pathnode->fdw_restrictinfo = fdw_restrictinfo;
23482354
pathnode->fdw_private = fdw_private;
23492355

23502356
return pathnode;
@@ -4149,6 +4155,8 @@ do { \
41494155
FLAT_COPY_PATH(fpath, path, ForeignPath);
41504156
if (fpath->fdw_outerpath)
41514157
REPARAMETERIZE_CHILD_PATH(fpath->fdw_outerpath);
4158+
if (fpath->fdw_restrictinfo)
4159+
ADJUST_CHILD_ATTRS(fpath->fdw_restrictinfo);
41524160

41534161
/* Hand over to FDW if needed. */
41544162
rfpc_func =
@@ -4166,6 +4174,8 @@ do { \
41664174

41674175
FLAT_COPY_PATH(cpath, path, CustomPath);
41684176
REPARAMETERIZE_CHILD_PATH_LIST(cpath->custom_paths);
4177+
if (cpath->custom_restrictinfo)
4178+
ADJUST_CHILD_ATTRS(cpath->custom_restrictinfo);
41694179
if (cpath->methods &&
41704180
cpath->methods->ReparameterizeCustomPathByChild)
41714181
cpath->custom_private =

0 commit comments

Comments
 (0)