From: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Parameterized append subpaths
Date: Wed, 09 Sep 2026 10:51:46 +0300
Message-ID: <588d117fb042bbae3a6e7b4379ab8ec7@postgrespro.ru> (raw)
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.cindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: a.pyhalov@postgrespro.ru, pgsql-hackers@lists.postgresql.org
Subject: Re: Parameterized append subpaths
In-Reply-To: <588d117fb042bbae3a6e7b4379ab8ec7@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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