Parameterized append subpaths

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