Parameterized append subpaths

Started by Alexander Pyhalov2 months ago3 messageshackers
Jump to latest
#1Alexander Pyhalov
a.pyhalov@postgrespro.ru

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:

t253723_1
0001-Preserve-all-differently-parameterized-paths.patchtext/x-diff; name=0001-Preserve-all-differently-parameterized-paths.patchDownload+4-17
#2Gleb Kashkin
g.kashkin@postgrespro.ru
In reply to: Alexander Pyhalov (#1)
Re: Parameterized append subpaths

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:

t253723_2
0001-Allow-FDWs-to-reparameterize-foreign-scan-paths.patchtext/x-diff; name=0001-Allow-FDWs-to-reparameterize-foreign-scan-paths.patchDownload+273-2
0002-postgres_fdw-cache-parameterized-path-cost-estimates.patchtext/x-diff; name=0002-postgres_fdw-cache-parameterized-path-cost-estimates.patchDownload+93-20
#3Etsuro Fujita
etsuro.fujita@gmail.com
In reply to: Gleb Kashkin (#2)
Re: Parameterized append subpaths

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]/messages/by-id/530C7464.6020006@lab.ntt.co.jp[2]/messages/by-id/532AE62A.6020307@lab.ntt.co.jp. Have you looked at it?

Best regards,
Etsuro Fujita

[1]: /messages/by-id/530C7464.6020006@lab.ntt.co.jp
[2]: /messages/by-id/532AE62A.6020307@lab.ntt.co.jp