pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedParameterized append subpaths
3+ messages / 3 participants
[nested] [flat]
* Parameterized append subpaths
@ 2026-09-09 07:51 Alexander Pyhalov <a.pyhalov@postgrespro.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Alexander Pyhalov @ 2026-09-09 07:51 UTC (permalink / raw)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Hi.
There's a (likely well known) add_path() issue - it can remove the path,
which otherwise would be useful in the future. Here is a a simplified
example:
create table t_local (i int);
SELECT current_database() AS current_database,
current_setting('port') AS current_port
\gset
CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname :'current_database', port :'current_port');
-- we need use_remote_estimate to make postgres_fdw generate
parameterized path.
alter server loopback options (add use_remote_estimate 'on');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
create table p1 (i int) partition by hash (i);
create table p1_part1 (i int);
create table p1_part2 (i int);
create index on p1_part1(i);
create index on p1_part2(i);
create foreign table p1_f1 partition of p1 for values with (modulus 2,
remainder 0) server loopback options (table_name 'p1_part1');
create foreign table p1_f2 partition of p1 for values with (modulus 2,
remainder 1) server loopback options (table_name 'p1_part2');
insert into p1 select i from generate_series(1,1000000) i;
insert into t_local values (1);
analyze t_local;
analyze p1_f1;
analyze p1_f2;
analyze p1;
In case of joining t_local and p1 we have the one good strategy -
parameterized nested loop.
explain analyze verbose select * from t_local, p1 where p1.i=t_local.i;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=100.43..210.36 rows=1 width=8) (actual
time=1.892..1.908 rows=1.00 loops=1)
Output: t_local.i, p1.i
Buffers: shared hit=1
-> Seq Scan on public.t_local (cost=0.00..1.01 rows=1 width=4)
(actual time=0.056..0.063 rows=1.00 loops=1)
Output: t_local.i
Buffers: shared hit=1
-> Append (cost=100.43..209.33 rows=2 width=4) (actual
time=1.821..1.828 rows=1.00 loops=1)
-> Foreign Scan on public.p1_f1 p1_1 (cost=100.43..104.66
rows=1 width=4) (actual time=1.802..1.805 rows=1.00 loops=1)
Output: p1_1.i
Remote SQL: SELECT i FROM public.p1_part1 WHERE ((i =
$1::integer))
-> Foreign Scan on public.p1_f2 p1_2 (cost=100.43..104.66
rows=1 width=4) (never executed)
Output: p1_2.i
Remote SQL: SELECT i FROM public.p1_part2 WHERE ((i =
$1::integer))
Planning Time: 8.514 ms
Execution Time: 2.655 ms
However, if we clean up one of the subpartitions
delete from public.p1_f2 ;
analyze p1_part2 ;
analyze p1_f2 ;
planner switches to more expensive hash join:
explain analyze verbose select * from t_local, p1 where p1.i=t_local.i;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=101.02..116651.67 rows=1 width=8) (actual
time=1.967..1739.119 rows=1.00 loops=1)
Output: t_local.i, p1.i
Hash Cond: (p1.i = t_local.i)
Buffers: shared hit=1
-> Append (cost=100.00..114777.98 rows=499376 width=4) (actual
time=1.876..1629.400 rows=499375.00 loops=1)
-> Foreign Scan on public.p1_f1 p1_1 (cost=100.00..112172.50
rows=499375 width=4) (actual time=1.874..1510.593 rows=499375.00
loops=1)
Output: p1_1.i
Remote SQL: SELECT i FROM public.p1_part1
-> Foreign Scan on public.p1_f2 p1_2 (cost=104.38..108.60
rows=1 width=4) (actual time=75.730..75.730 rows=0.00 loops=1)
Output: p1_2.i
Remote SQL: SELECT i FROM public.p1_part2
-> Hash (cost=1.01..1.01 rows=1 width=4) (actual time=0.064..0.065
rows=1.00 loops=1)
Output: t_local.i
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on public.t_local (cost=0.00..1.01 rows=1
width=4) (actual time=0.051..0.054 rows=1.00 loops=1)
Output: t_local.i
Buffers: shared hit=1
Planning:
Buffers: shared hit=11
Planning Time: 8.581 ms
Execution Time: 1739.564 ms
What happens here? When we construct Append subpaths, firstly we
generate non-parameterized foreign scan paths for p1_f2 without
pathkeys:
startup_cost = 100, total_cost = 100.21000000000001
Later we generate path with pathkeys, which we consider more
interesting:
startup_cost = 100.01000000000001, total_cost = 100.23
When we are looking at possible parameterized path
startup_cost = 100.01000000000001, total_cost = 100.22000000000001,
pathkeys = 0x0,
new_path->param_info->ppi_req_outer contains t_local relid,
new path has essentially the same cost as the old paths. add_path()
considers new and old path and prefers to keep non-parameterized path
(especially if old path has pathkeys) as new_path->rows = old_path->rows
= 1 (as we have no rows in this section),
and we conclude that shouldn't accept new path.
Later we cannot create parameterized append path as one of its children
lacks parameterized paths, and so can't use parameterized nest loop.
So, it seems we've preferred HashJoin to parameterized NestLoop not
based on their costs, but based on missing parameterized path. Perhaps,
we should
keep all differently parameterized paths?
Attaching WIP patch which tries to fix this issue.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
Attachments:
[text/x-diff] 0001-Preserve-all-differently-parameterized-paths.patch (2.8K, ../../588d117fb042bbae3a6e7b4379ab8ec7@postgrespro.ru/2-0001-Preserve-all-differently-parameterized-paths.patch)
download | inline diff:
From 4e118470569debef5d5069ad1b616f88c42d898b Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
Date: Wed, 9 Sep 2026 10:44:52 +0300
Subject: [PATCH] Preserve all differently parameterized paths
---
src/backend/optimizer/util/pathnode.c | 20 ++++----------------
1 file changed, 4 insertions(+), 16 deletions(-)
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 2ba31765ca5..9bc44a210e5 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -519,16 +519,14 @@ add_path(RelOptInfo *parent_rel, Path *new_path)
PATH_REQ_OUTER(old_path));
if (keyscmp == PATHKEYS_BETTER1)
{
- if ((outercmp == BMS_EQUAL ||
- outercmp == BMS_SUBSET1) &&
+ if (outercmp == BMS_EQUAL &&
new_path->rows <= old_path->rows &&
new_path->parallel_safe >= old_path->parallel_safe)
remove_old = true; /* new dominates old */
}
else if (keyscmp == PATHKEYS_BETTER2)
{
- if ((outercmp == BMS_EQUAL ||
- outercmp == BMS_SUBSET2) &&
+ if (outercmp == BMS_EQUAL &&
new_path->rows >= old_path->rows &&
new_path->parallel_safe <= old_path->parallel_safe)
accept_new = false; /* old dominates new */
@@ -570,14 +568,6 @@ add_path(RelOptInfo *parent_rel, Path *new_path)
accept_new = false; /* old equals or
* dominates new */
}
- else if (outercmp == BMS_SUBSET1 &&
- new_path->rows <= old_path->rows &&
- new_path->parallel_safe >= old_path->parallel_safe)
- remove_old = true; /* new dominates old */
- else if (outercmp == BMS_SUBSET2 &&
- new_path->rows >= old_path->rows &&
- new_path->parallel_safe <= old_path->parallel_safe)
- accept_new = false; /* old dominates new */
/* else different parameterizations, keep both */
}
break;
@@ -586,8 +576,7 @@ add_path(RelOptInfo *parent_rel, Path *new_path)
{
outercmp = bms_subset_compare(PATH_REQ_OUTER(new_path),
PATH_REQ_OUTER(old_path));
- if ((outercmp == BMS_EQUAL ||
- outercmp == BMS_SUBSET1) &&
+ if (outercmp == BMS_EQUAL &&
new_path->rows <= old_path->rows &&
new_path->parallel_safe >= old_path->parallel_safe)
remove_old = true; /* new dominates old */
@@ -598,8 +587,7 @@ add_path(RelOptInfo *parent_rel, Path *new_path)
{
outercmp = bms_subset_compare(PATH_REQ_OUTER(new_path),
PATH_REQ_OUTER(old_path));
- if ((outercmp == BMS_EQUAL ||
- outercmp == BMS_SUBSET2) &&
+ if (outercmp == BMS_EQUAL &&
new_path->rows >= old_path->rows &&
new_path->parallel_safe <= old_path->parallel_safe)
accept_new = false; /* old dominates new */
--
2.43.0
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Parameterized append subpaths
@ 2026-09-23 09:11 Gleb Kashkin <g.kashkin@postgrespro.ru>
parent: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Gleb Kashkin @ 2026-09-23 09:11 UTC (permalink / raw)
To: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Alexander Pyhalov писал(а) 2026-09-09 10:51:
> There's a (likely well known) add_path() issue - it can remove the
> path, which otherwise would be useful in the future
Hi!
We found another way to address the issue. It could be better suited for
current path selection model:
Instead of keeping all parameterized foreign paths, we could
re-parameterize the ones that were left after add_path() domination, as
is already done with other path types. This requires a new FDW interface
function, but allows to keep current path domination logic and build
additional foreign paths only when they are truly required, like in
parametrized NestLoop.
The first attached patch implements path reparameterization in
postgres_fwd and adds it to reparameterize_path().
The second patch is optional, it adds a cache for parameterized paths in
postgres_fdw, so there is no excessive roundtrip for remote estimate
EXPLAIN for paths that have already been costed and rejected.
--
Best regards,
Gleb Kashkin,
Postgres Professional
Attachments:
[text/x-diff] 0001-Allow-FDWs-to-reparameterize-foreign-scan-paths.patch (16.8K, ../../98f84e7438c5b78a5a1c77539ffc7e0b@postgrespro.ru/2-0001-Allow-FDWs-to-reparameterize-foreign-scan-paths.patch)
download | inline diff:
From aac253927a8b65d1d08e27a1a2bce4c9320dc57e Mon Sep 17 00:00:00 2001
From: Gleb Kashkin <g.kashkin@postgrespro.ru>
Date: Tue, 22 Sep 2026 17:29:43 +0300
Subject: [PATCH 1/2] Allow FDWs to reparameterize foreign scan paths
When building a parameterized Append over a partitioned table, every
child must have a path with exactly the requested parameterization.
For local scans, get_cheapest_parameterized_child_path() can obtain one
by calling reparameterize_path() on an existing path, but foreign paths
were not handled there, because core has no way to cost a foreign scan.
So if add_path() had discarded a foreign partition's parameterized path
(e.g. because the partition is empty and the path was dominated by the
unparameterized one), no parameterized Append could be built at all and
the planner was forced into a hash or merge join even when a
parameterized nestloop would have been far cheaper.
Add an optional ReparameterizeForeignPath callback to FdwRoutine, which
reparameterize_path() invokes for foreign paths of simple relations, and
implement it in postgres_fdw: the callback looks up the ParamPathInfo
for the requested outer rels, costs its clauses the same way
postgresGetForeignPaths() does, and returns a new unsorted parameterized
ForeignPath.
---
.../postgres_fdw/expected/postgres_fdw.out | 103 ++++++++++++++++++
contrib/postgres_fdw/postgres_fdw.c | 72 ++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 49 +++++++++
doc/src/sgml/fdwhandler.sgml | 35 +++++-
src/backend/optimizer/util/pathnode.c | 11 ++
src/include/foreign/fdwapi.h | 4 +
6 files changed, 273 insertions(+), 1 deletion(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index e90289e4ab1..e6b17800fe5 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -4581,6 +4581,109 @@ REINDEX TABLE reind_fdw_parent; -- ok
REINDEX TABLE CONCURRENTLY reind_fdw_parent; -- ok
DROP TABLE reind_fdw_parent;
-- ===================================================================
+-- parameterized Append paths over foreign partitions
+-- ===================================================================
+-- With use_remote_estimate, each foreign partition gets a path parameterized
+-- by the join clause. If add_path() discards that path for some partition
+-- (here: because the partition is empty, so the parameterized path is
+-- dominated by the unparameterized one), the planner must still be able to
+-- build a parameterized Append by reparameterizing the surviving path.
+CREATE TABLE pa_loc1 (a int);
+CREATE TABLE pa_loc2 (a int);
+CREATE INDEX ON pa_loc1 (a);
+CREATE INDEX ON pa_loc2 (a);
+CREATE TABLE pa_parent (a int) PARTITION BY HASH (a);
+CREATE FOREIGN TABLE pa_f1 PARTITION OF pa_parent
+ FOR VALUES WITH (MODULUS 2, REMAINDER 0)
+ SERVER loopback OPTIONS (table_name 'pa_loc1', use_remote_estimate 'true');
+CREATE FOREIGN TABLE pa_f2 PARTITION OF pa_parent
+ FOR VALUES WITH (MODULUS 2, REMAINDER 1)
+ SERVER loopback OPTIONS (table_name 'pa_loc2', use_remote_estimate 'true');
+INSERT INTO pa_loc1 SELECT g FROM generate_series(1, 100000) g
+ WHERE satisfies_hash_partition('pa_parent'::regclass, 2, 0, g);
+INSERT INTO pa_loc2 SELECT g FROM generate_series(1, 100000) g
+ WHERE satisfies_hash_partition('pa_parent'::regclass, 2, 1, g);
+CREATE TABLE pa_outer (a int);
+INSERT INTO pa_outer VALUES (1);
+ANALYZE pa_loc1;
+ANALYZE pa_loc2;
+ANALYZE pa_f1;
+ANALYZE pa_f2;
+ANALYZE pa_outer;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM pa_outer o JOIN pa_parent p ON p.a = o.a;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Nested Loop
+ Output: o.a, p.a
+ -> Seq Scan on public.pa_outer o
+ Output: o.a
+ -> Append
+ -> Foreign Scan on public.pa_f1 p_1
+ Output: p_1.a
+ Remote SQL: SELECT a FROM public.pa_loc1 WHERE ((a = $1::integer))
+ -> Foreign Scan on public.pa_f2 p_2
+ Output: p_2.a
+ Remote SQL: SELECT a FROM public.pa_loc2 WHERE ((a = $1::integer))
+(11 rows)
+
+-- Empty one partition; its parameterized path is now dominated and dropped.
+DELETE FROM pa_f2;
+ANALYZE pa_loc2;
+ANALYZE pa_f2;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM pa_outer o JOIN pa_parent p ON p.a = o.a;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Nested Loop
+ Output: o.a, p.a
+ -> Seq Scan on public.pa_outer o
+ Output: o.a
+ -> Append
+ -> Foreign Scan on public.pa_f1 p_1
+ Output: p_1.a
+ Remote SQL: SELECT a FROM public.pa_loc1 WHERE ((a = $1::integer))
+ -> Foreign Scan on public.pa_f2 p_2
+ Output: p_2.a
+ Remote SQL: SELECT a FROM public.pa_loc2 WHERE ((a = $1::integer))
+(11 rows)
+
+SELECT * FROM pa_outer o JOIN pa_parent p ON p.a = o.a;
+ a | a
+---+---
+ 1 | 1
+(1 row)
+
+-- Once the remote table is known to be empty, the sorted and unsorted plain
+-- scans are fuzzily equal in cost, so only the sorted one survives; we must
+-- be able to reparameterize that one, too.
+VACUUM ANALYZE pa_loc2;
+ANALYZE pa_f2;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM pa_outer o JOIN pa_parent p ON p.a = o.a;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Nested Loop
+ Output: o.a, p.a
+ -> Seq Scan on public.pa_outer o
+ Output: o.a
+ -> Append
+ -> Foreign Scan on public.pa_f1 p_1
+ Output: p_1.a
+ Remote SQL: SELECT a FROM public.pa_loc1 WHERE ((a = $1::integer))
+ -> Foreign Scan on public.pa_f2 p_2
+ Output: p_2.a
+ Remote SQL: SELECT a FROM public.pa_loc2 WHERE ((a = $1::integer))
+(11 rows)
+
+SELECT * FROM pa_outer o JOIN pa_parent p ON p.a = o.a;
+ a | a
+---+---
+ 1 | 1
+(1 row)
+
+DROP TABLE pa_parent, pa_outer, pa_loc1, pa_loc2;
+-- ===================================================================
-- conversion error
-- ===================================================================
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE int;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 0a589f8db74..929dd129480 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -593,6 +593,9 @@ static void postgresGetForeignJoinPaths(PlannerInfo *root,
RelOptInfo *innerrel,
JoinType jointype,
JoinPathExtraData *extra);
+static Path *postgresReparameterizeForeignPath(PlannerInfo *root,
+ ForeignPath *path,
+ Relids required_outer);
static bool postgresRecheckForeignScan(ForeignScanState *node,
TupleTableSlot *slot);
static void postgresGetForeignUpperPaths(PlannerInfo *root,
@@ -821,6 +824,9 @@ postgres_fdw_handler(PG_FUNCTION_ARGS)
/* Support functions for upper relation push-down */
routine->GetForeignUpperPaths = postgresGetForeignUpperPaths;
+ /* Support functions for path reparameterization */
+ routine->ReparameterizeForeignPath = postgresReparameterizeForeignPath;
+
/* Support functions for asynchronous execution */
routine->IsForeignPathAsyncCapable = postgresIsForeignPathAsyncCapable;
routine->ForeignAsyncRequest = postgresForeignAsyncRequest;
@@ -1438,6 +1444,72 @@ postgresGetForeignPaths(PlannerInfo *root,
}
}
+/*
+ * postgresReparameterizeForeignPath
+ * Build a version of a base-relation foreign scan path that is
+ * parameterized by required_outer, i.e. that additionally enforces the
+ * join clauses available from those relations.
+ */
+static Path *
+postgresReparameterizeForeignPath(PlannerInfo *root, ForeignPath *path,
+ Relids required_outer)
+{
+ RelOptInfo *baserel = path->path.parent;
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) baserel->fdw_private;
+ ParamPathInfo *param_info;
+ double rows;
+ int width;
+ int disabled_nodes;
+ Cost startup_cost;
+ Cost total_cost;
+
+ Assert(IS_SIMPLE_REL(baserel));
+
+ /*
+ * No way to get a good estimate on pushed down clauses, don't build
+ * parameterized paths.
+ */
+ if (!fpinfo->use_remote_estimate)
+ return NULL;
+
+ /*
+ * We don't know how to carry an EPQ subplan along, but base-relation
+ * paths never have one anyway.
+ */
+ if (path->fdw_outerpath != NULL)
+ return NULL;
+
+ /*
+ * Note that we ignore the given path's pathkeys and always produce an
+ * unsorted path. A parameterized path is only ever used on the inside
+ * of a NestLoop, where its ordering is of no interest.
+ */
+
+ param_info = get_baserel_parampathinfo(root, baserel, required_outer);
+ if (param_info == NULL)
+ return NULL; /* shouldn't happen */
+
+ /* Get a cost estimate from the remote */
+ estimate_path_cost_size(root, baserel,
+ param_info->ppi_clauses, NIL, NULL,
+ &rows, &width, &disabled_nodes,
+ &startup_cost, &total_cost);
+
+ param_info->ppi_rows = rows;
+
+ return (Path *) create_foreignscan_path(root, baserel,
+ NULL, /* default pathtarget */
+ rows,
+ disabled_nodes,
+ startup_cost,
+ total_cost,
+ NIL, /* no pathkeys */
+ param_info->ppi_req_outer,
+ NULL,
+ NIL, /* no fdw_restrictinfo list */
+ NIL); /* no fdw_private list */
+}
+
/*
* postgresGetForeignPlan
* Create ForeignScan plan node which implements selected best path
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index dfc58beb0d2..2ccdb30e2a2 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1315,6 +1315,55 @@ REINDEX TABLE reind_fdw_parent; -- ok
REINDEX TABLE CONCURRENTLY reind_fdw_parent; -- ok
DROP TABLE reind_fdw_parent;
+-- ===================================================================
+-- parameterized Append paths over foreign partitions
+-- ===================================================================
+-- With use_remote_estimate, each foreign partition gets a path parameterized
+-- by the join clause. If add_path() discards that path for some partition
+-- (here: because the partition is empty, so the parameterized path is
+-- dominated by the unparameterized one), the planner must still be able to
+-- build a parameterized Append by reparameterizing the surviving path.
+CREATE TABLE pa_loc1 (a int);
+CREATE TABLE pa_loc2 (a int);
+CREATE INDEX ON pa_loc1 (a);
+CREATE INDEX ON pa_loc2 (a);
+CREATE TABLE pa_parent (a int) PARTITION BY HASH (a);
+CREATE FOREIGN TABLE pa_f1 PARTITION OF pa_parent
+ FOR VALUES WITH (MODULUS 2, REMAINDER 0)
+ SERVER loopback OPTIONS (table_name 'pa_loc1', use_remote_estimate 'true');
+CREATE FOREIGN TABLE pa_f2 PARTITION OF pa_parent
+ FOR VALUES WITH (MODULUS 2, REMAINDER 1)
+ SERVER loopback OPTIONS (table_name 'pa_loc2', use_remote_estimate 'true');
+INSERT INTO pa_loc1 SELECT g FROM generate_series(1, 100000) g
+ WHERE satisfies_hash_partition('pa_parent'::regclass, 2, 0, g);
+INSERT INTO pa_loc2 SELECT g FROM generate_series(1, 100000) g
+ WHERE satisfies_hash_partition('pa_parent'::regclass, 2, 1, g);
+CREATE TABLE pa_outer (a int);
+INSERT INTO pa_outer VALUES (1);
+ANALYZE pa_loc1;
+ANALYZE pa_loc2;
+ANALYZE pa_f1;
+ANALYZE pa_f2;
+ANALYZE pa_outer;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM pa_outer o JOIN pa_parent p ON p.a = o.a;
+-- Empty one partition; its parameterized path is now dominated and dropped.
+DELETE FROM pa_f2;
+ANALYZE pa_loc2;
+ANALYZE pa_f2;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM pa_outer o JOIN pa_parent p ON p.a = o.a;
+SELECT * FROM pa_outer o JOIN pa_parent p ON p.a = o.a;
+-- Once the remote table is known to be empty, the sorted and unsorted plain
+-- scans are fuzzily equal in cost, so only the sorted one survives; we must
+-- be able to reparameterize that one, too.
+VACUUM ANALYZE pa_loc2;
+ANALYZE pa_f2;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM pa_outer o JOIN pa_parent p ON p.a = o.a;
+SELECT * FROM pa_outer o JOIN pa_parent p ON p.a = o.a;
+DROP TABLE pa_parent, pa_outer, pa_loc1, pa_loc2;
+
-- ===================================================================
-- conversion error
-- ===================================================================
diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml
index 8685a078c52..a04a113ef2c 100644
--- a/doc/src/sgml/fdwhandler.sgml
+++ b/doc/src/sgml/fdwhandler.sgml
@@ -1721,6 +1721,36 @@ ReparameterizeForeignPathByChild(PlannerInfo *root, List *fdw_private,
<literal>adjust_appendrel_attrs</literal> or
<literal>adjust_appendrel_attrs_multilevel</literal> as required.
</para>
+
+ <para>
+<programlisting>
+Path *
+ReparameterizeForeignPath(PlannerInfo *root, ForeignPath *path,
+ Relids required_outer);
+</programlisting>
+ This function is called when the planner needs a version of an existing
+ base-relation <structname>ForeignPath</structname> that is parameterized
+ by the relations in <literal>required_outer</literal>, which is a
+ superset of the parameterization of <literal>path</literal>. This
+ happens, for example, while building a parameterized
+ <literal>Append</literal> path over a partitioned table with foreign
+ partitions, since all children of such a path must have exactly the same
+ parameterization. The function must return a new
+ <structname>ForeignPath</structname> whose <literal>param_info</literal>
+ is the value obtained from <function>get_baserel_parampathinfo</function>
+ for <literal>required_outer</literal>, and whose row count and cost
+ estimates account for the additional join clauses found in that value's
+ <structfield>ppi_clauses</structfield>; all of those clauses must be
+ enforced by the returned path, either remotely or locally. It must
+ return <literal>NULL</literal> if no such path can be built, and it must
+ not pass the returned path to <function>add_path</function>.
+ </para>
+
+ <para>
+ This function is optional. If it is not provided, the planner cannot
+ build a parameterized <literal>Append</literal> path unless every foreign
+ child relation already has a path with the required parameterization.
+ </para>
</sect2>
</sect1>
@@ -2014,7 +2044,10 @@ GetForeignServerByName(const char *name, bool missing_ok);
to compute that value. In <function>GetForeignPlan</function>, the
<replaceable>local_variable</replaceable> portion of the join clause would be added
to <structfield>fdw_exprs</structfield>, and then at run time the case works the
- same as for an ordinary restriction clause.
+ same as for an ordinary restriction clause. If the FDW provides
+ <function>ReparameterizeForeignPath</function>, the planner can also ask
+ it after the fact for a version of an existing path that uses additional
+ join clauses; see <xref linkend="fdw-callbacks-reparameterize-paths"/>.
</para>
<para>
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 73518c8f870..51f4c38a8a6 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -4053,6 +4053,17 @@ reparameterize_path(PlannerInfo *root, Path *path,
mpath->binary_mode,
mpath->est_calls);
}
+ case T_ForeignScan:
+ {
+ ReparameterizeForeignPath_function rfp_func;
+
+ if (!IS_SIMPLE_REL(rel) || rel->fdwroutine == NULL)
+ break;
+ rfp_func = rel->fdwroutine->ReparameterizeForeignPath;
+ if (rfp_func == NULL)
+ break;
+ return rfp_func(root, (ForeignPath *) path, required_outer);
+ }
default:
break;
}
diff --git a/src/include/foreign/fdwapi.h b/src/include/foreign/fdwapi.h
index abf59a0d8ad..7a88165919a 100644
--- a/src/include/foreign/fdwapi.h
+++ b/src/include/foreign/fdwapi.h
@@ -186,6 +186,9 @@ typedef bool (*IsForeignScanParallelSafe_function) (PlannerInfo *root,
typedef List *(*ReparameterizeForeignPathByChild_function) (PlannerInfo *root,
List *fdw_private,
RelOptInfo *child_rel);
+typedef Path *(*ReparameterizeForeignPath_function) (PlannerInfo *root,
+ ForeignPath *path,
+ Relids required_outer);
typedef bool (*IsForeignPathAsyncCapable_function) (ForeignPath *path);
@@ -277,6 +280,7 @@ typedef struct FdwRoutine
/* Support functions for path reparameterization. */
ReparameterizeForeignPathByChild_function ReparameterizeForeignPathByChild;
+ ReparameterizeForeignPath_function ReparameterizeForeignPath;
/* Support functions for asynchronous execution */
IsForeignPathAsyncCapable_function IsForeignPathAsyncCapable;
--
2.55.0
[text/x-diff] 0002-postgres_fdw-cache-parameterized-path-cost-estimates.patch (6.9K, ../../98f84e7438c5b78a5a1c77539ffc7e0b@postgrespro.ru/3-0002-postgres_fdw-cache-parameterized-path-cost-estimates.patch)
download | inline diff:
From 8a7bababfd39b40e32f8fff7ef1a8989138ed3e6 Mon Sep 17 00:00:00 2001
From: Gleb Kashkin <g.kashkin@postgrespro.ru>
Date: Tue, 22 Sep 2026 17:29:43 +0300
Subject: [PATCH 2/2] postgres_fdw: cache parameterized-path cost estimates
In use_remote_estimate mode, every estimate for a parameterized scan
costs a remote EXPLAIN, and with the new ReparameterizeForeignPath
callback the planner may ask for the same parameterization repeatedly:
once in postgresGetForeignPaths(), and later once per surviving path of
the relation from get_cheapest_parameterized_child_path().
Remember the estimates in a per-relation list keyed by the (interned)
ParamPathInfo, so that each parameterization is costed at most once per
relation. In the common case, where the parameterized path was already
built and merely lost the add_path() tournament, reparameterizing it
now issues no remote queries at all.
---
contrib/postgres_fdw/postgres_fdw.c | 105 +++++++++++++++++++++++-----
contrib/postgres_fdw/postgres_fdw.h | 7 ++
2 files changed, 93 insertions(+), 19 deletions(-)
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 929dd129480..cea5c0811f8 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -278,6 +278,23 @@ typedef struct PgFdwAnalyzeState
MemoryContext temp_cxt; /* context for per-tuple temporary data */
} PgFdwAnalyzeState;
+/*
+ * Cached size and cost estimate for a parameterized scan of a base relation,
+ * keyed by its (interned) ParamPathInfo. These are kept in the
+ * param_path_costs list of the relation's PgFdwRelationInfo, so that
+ * reparameterizing a path does not require a second remote EXPLAIN for a
+ * parameterization we already costed.
+ */
+typedef struct PgFdwParamPathCost
+{
+ ParamPathInfo *param_info;
+ double rows;
+ int width;
+ int disabled_nodes;
+ Cost startup_cost;
+ Cost total_cost;
+} PgFdwParamPathCost;
+
/*
* This enum describes what's kept in the fdw_private list for a ForeignPath.
* We store:
@@ -611,6 +628,12 @@ static void postgresForeignAsyncNotify(AsyncRequest *areq);
/*
* Helper functions
*/
+static void get_param_path_cost(PlannerInfo *root,
+ RelOptInfo *baserel,
+ ParamPathInfo *param_info,
+ double *p_rows, int *p_width,
+ int *p_disabled_nodes,
+ Cost *p_startup_cost, Cost *p_total_cost);
static void estimate_path_cost_size(PlannerInfo *root,
RelOptInfo *foreignrel,
List *param_join_conds,
@@ -880,6 +903,9 @@ postgresGetForeignRelSize(PlannerInfo *root,
apply_server_options(fpinfo);
apply_table_options(fpinfo);
+ /* No parameterized-path estimates cached yet. */
+ fpinfo->param_path_costs = NIL;
+
/*
* If the table or the server is configured to use remote estimates,
* identify which user to do remote access as during planning. This
@@ -1416,17 +1442,10 @@ postgresGetForeignPaths(PlannerInfo *root,
Cost startup_cost;
Cost total_cost;
- /* Get a cost estimate from the remote */
- estimate_path_cost_size(root, baserel,
- param_info->ppi_clauses, NIL, NULL,
- &rows, &width, &disabled_nodes,
- &startup_cost, &total_cost);
-
- /*
- * ppi_rows currently won't get looked at by anything, but still we
- * may as well ensure that it matches our idea of the rowcount.
- */
- param_info->ppi_rows = rows;
+ /* Get a cost estimate from the remote (or from our cache) */
+ get_param_path_cost(root, baserel, param_info,
+ &rows, &width, &disabled_nodes,
+ &startup_cost, &total_cost);
/* Make the path */
path = create_foreignscan_path(root, baserel,
@@ -1444,6 +1463,55 @@ postgresGetForeignPaths(PlannerInfo *root,
}
}
+/*
+ * get_param_path_cost
+ * Estimate the size and cost of scanning baserel with the join clauses
+ * of the given ParamPathInfo pushed down, caching the result.
+ */
+static void
+get_param_path_cost(PlannerInfo *root, RelOptInfo *baserel,
+ ParamPathInfo *param_info,
+ double *p_rows, int *p_width, int *p_disabled_nodes,
+ Cost *p_startup_cost, Cost *p_total_cost)
+{
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) baserel->fdw_private;
+ PgFdwParamPathCost *ppc = NULL;
+ ListCell *lc;
+
+ foreach(lc, fpinfo->param_path_costs)
+ {
+ PgFdwParamPathCost *cached = (PgFdwParamPathCost *) lfirst(lc);
+
+ if (cached->param_info == param_info)
+ {
+ ppc = cached;
+ break;
+ }
+ }
+
+ if (ppc == NULL)
+ {
+ ppc = (PgFdwParamPathCost *) palloc(sizeof(PgFdwParamPathCost));
+ ppc->param_info = param_info;
+
+ /* Get a cost estimate from the remote */
+ estimate_path_cost_size(root, baserel,
+ param_info->ppi_clauses, NIL, NULL,
+ &ppc->rows, &ppc->width, &ppc->disabled_nodes,
+ &ppc->startup_cost, &ppc->total_cost);
+
+ param_info->ppi_rows = ppc->rows;
+
+ fpinfo->param_path_costs = lappend(fpinfo->param_path_costs, ppc);
+ }
+
+ *p_rows = ppc->rows;
+ *p_width = ppc->width;
+ *p_disabled_nodes = ppc->disabled_nodes;
+ *p_startup_cost = ppc->startup_cost;
+ *p_total_cost = ppc->total_cost;
+}
+
/*
* postgresReparameterizeForeignPath
* Build a version of a base-relation foreign scan path that is
@@ -1482,20 +1550,19 @@ postgresReparameterizeForeignPath(PlannerInfo *root, ForeignPath *path,
/*
* Note that we ignore the given path's pathkeys and always produce an
* unsorted path. A parameterized path is only ever used on the inside
- * of a NestLoop, where its ordering is of no interest.
+ * of a NestLoop, where its ordering is of no interest. Producing an
+ * unsorted path also means that we get to reuse the cost estimate we
+ * already made for this parameterization, if any.
*/
param_info = get_baserel_parampathinfo(root, baserel, required_outer);
if (param_info == NULL)
return NULL; /* shouldn't happen */
- /* Get a cost estimate from the remote */
- estimate_path_cost_size(root, baserel,
- param_info->ppi_clauses, NIL, NULL,
- &rows, &width, &disabled_nodes,
- &startup_cost, &total_cost);
-
- param_info->ppi_rows = rows;
+ /* Get a cost estimate from the remote (or from our cache) */
+ get_param_path_cost(root, baserel, param_info,
+ &rows, &width, &disabled_nodes,
+ &startup_cost, &total_cost);
return (Path *) create_foreignscan_path(root, baserel,
NULL, /* default pathtarget */
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index a2bb1ff352c..9d61c0216cf 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -75,6 +75,13 @@ typedef struct PgFdwRelationInfo
Cost rel_startup_cost;
Cost rel_total_cost;
+ /*
+ * Cached estimates for parameterized scans of a base relation, one
+ * entry per ParamPathInfo (list of PgFdwParamPathCost). Only used in
+ * use_remote_estimate mode.
+ */
+ List *param_path_costs;
+
/* Options extracted from catalogs. */
bool use_remote_estimate;
Cost fdw_startup_cost;
--
2.55.0
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Parameterized append subpaths
@ 2026-09-23 11:05 Etsuro Fujita <etsuro.fujita@gmail.com>
parent: Gleb Kashkin <g.kashkin@postgrespro.ru>
0 siblings, 0 replies; 3+ messages in thread
From: Etsuro Fujita @ 2026-09-23 11:05 UTC (permalink / raw)
To: Gleb Kashkin <g.kashkin@postgrespro.ru>; +Cc: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Hi Gleb,
On Wed, Sep 23, 2026 at 6:12 PM Gleb Kashkin <g.kashkin@postgrespro.ru> wrote:
> We found another way to address the issue. It could be better suited for
> current path selection model:
> Instead of keeping all parameterized foreign paths, we could
> re-parameterize the ones that were left after add_path() domination, as
> is already done with other path types. This requires a new FDW interface
> function, but allows to keep current path domination logic and build
> additional foreign paths only when they are truly required, like in
> parametrized NestLoop.
>
> The first attached patch implements path reparameterization in
> postgres_fwd and adds it to reparameterize_path().
> The second patch is optional, it adds a cache for parameterized paths in
> postgres_fdw, so there is no excessive roundtrip for remote estimate
> EXPLAIN for paths that have already been costed and rejected.
+1
I proposed this FDW API a long time ago as part of the
foreign-table-inheritance work [1][2]. Have you looked at it?
Best regards,
Etsuro Fujita
[1] https://www.postgresql.org/message-id/530C7464.6020006%40lab.ntt.co.jp
[2] https://www.postgresql.org/message-id/532AE62A.6020307%40lab.ntt.co.jp
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-09-23 11:05 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-09 07:51 Parameterized append subpaths Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2026-09-23 09:11 ` Gleb Kashkin <g.kashkin@postgrespro.ru>
2026-09-23 11:05 ` Etsuro Fujita <etsuro.fujita@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox