MIME-Version: 1.0
Date: Wed, 09 Sep 2026 10:51:46 +0300
From: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Parameterized append subpaths
Message-ID: <588d117fb042bbae3a6e7b4379ab8ec7@postgrespro.ru>
Content-Type: multipart/mixed;
 boundary="=_fa8278efaf2b84c81fd938bb8f9e065b"
Archived-At: 
 <https://www.postgresql.org/message-id/588d117fb042bbae3a6e7b4379ab8ec7%40postgrespro.ru>
Precedence: bulk

--=_fa8278efaf2b84c81fd938bb8f9e065b
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII;
 format=flowed

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
--=_fa8278efaf2b84c81fd938bb8f9e065b
Content-Transfer-Encoding: base64
Content-Type: text/x-diff;
 name=0001-Preserve-all-differently-parameterized-paths.patch
Content-Disposition: attachment;
 filename=0001-Preserve-all-differently-parameterized-paths.patch;
 size=2911

RnJvbSA0ZTExODQ3MDU2OWRlYmVmNWQ1MDY5YWQxYjYxNmY4OGM0MmQ4OThiIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBBbGV4YW5kZXIgUHloYWxvdiA8YS5weWhhbG92QHBvc3RncmVz
cHJvLnJ1PgpEYXRlOiBXZWQsIDkgU2VwIDIwMjYgMTA6NDQ6NTIgKzAzMDAKU3ViamVjdDogW1BB
VENIXSBQcmVzZXJ2ZSBhbGwgZGlmZmVyZW50bHkgcGFyYW1ldGVyaXplZCBwYXRocwoKLS0tCiBz
cmMvYmFja2VuZC9vcHRpbWl6ZXIvdXRpbC9wYXRobm9kZS5jIHwgMjAgKysrKy0tLS0tLS0tLS0t
LS0tLS0KIDEgZmlsZSBjaGFuZ2VkLCA0IGluc2VydGlvbnMoKyksIDE2IGRlbGV0aW9ucygtKQoK
ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci91dGlsL3BhdGhub2RlLmMgYi9zcmMv
YmFja2VuZC9vcHRpbWl6ZXIvdXRpbC9wYXRobm9kZS5jCmluZGV4IDJiYTMxNzY1Y2E1Li45YmM0
NGEyMTBlNSAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3V0aWwvcGF0aG5vZGUu
YworKysgYi9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvdXRpbC9wYXRobm9kZS5jCkBAIC01MTksMTYg
KzUxOSwxNCBAQCBhZGRfcGF0aChSZWxPcHRJbmZvICpwYXJlbnRfcmVsLCBQYXRoICpuZXdfcGF0
aCkKIAkJCQkJCQkJCQkJCQkgIFBBVEhfUkVRX09VVEVSKG9sZF9wYXRoKSk7CiAJCQkJCQlpZiAo
a2V5c2NtcCA9PSBQQVRIS0VZU19CRVRURVIxKQogCQkJCQkJewotCQkJCQkJCWlmICgob3V0ZXJj
bXAgPT0gQk1TX0VRVUFMIHx8Ci0JCQkJCQkJCSBvdXRlcmNtcCA9PSBCTVNfU1VCU0VUMSkgJiYK
KwkJCQkJCQlpZiAob3V0ZXJjbXAgPT0gQk1TX0VRVUFMICYmCiAJCQkJCQkJCW5ld19wYXRoLT5y
b3dzIDw9IG9sZF9wYXRoLT5yb3dzICYmCiAJCQkJCQkJCW5ld19wYXRoLT5wYXJhbGxlbF9zYWZl
ID49IG9sZF9wYXRoLT5wYXJhbGxlbF9zYWZlKQogCQkJCQkJCQlyZW1vdmVfb2xkID0gdHJ1ZTsJ
LyogbmV3IGRvbWluYXRlcyBvbGQgKi8KIAkJCQkJCX0KIAkJCQkJCWVsc2UgaWYgKGtleXNjbXAg
PT0gUEFUSEtFWVNfQkVUVEVSMikKIAkJCQkJCXsKLQkJCQkJCQlpZiAoKG91dGVyY21wID09IEJN
U19FUVVBTCB8fAotCQkJCQkJCQkgb3V0ZXJjbXAgPT0gQk1TX1NVQlNFVDIpICYmCisJCQkJCQkJ
aWYgKG91dGVyY21wID09IEJNU19FUVVBTCAmJgogCQkJCQkJCQluZXdfcGF0aC0+cm93cyA+PSBv
bGRfcGF0aC0+cm93cyAmJgogCQkJCQkJCQluZXdfcGF0aC0+cGFyYWxsZWxfc2FmZSA8PSBvbGRf
cGF0aC0+cGFyYWxsZWxfc2FmZSkKIAkJCQkJCQkJYWNjZXB0X25ldyA9IGZhbHNlOyAvKiBvbGQg
ZG9taW5hdGVzIG5ldyAqLwpAQCAtNTcwLDE0ICs1NjgsNiBAQCBhZGRfcGF0aChSZWxPcHRJbmZv
ICpwYXJlbnRfcmVsLCBQYXRoICpuZXdfcGF0aCkKIAkJCQkJCQkJCWFjY2VwdF9uZXcgPSBmYWxz
ZTsgLyogb2xkIGVxdWFscyBvcgogCQkJCQkJCQkJCQkJCQkgKiBkb21pbmF0ZXMgbmV3ICovCiAJ
CQkJCQkJfQotCQkJCQkJCWVsc2UgaWYgKG91dGVyY21wID09IEJNU19TVUJTRVQxICYmCi0JCQkJ
CQkJCQkgbmV3X3BhdGgtPnJvd3MgPD0gb2xkX3BhdGgtPnJvd3MgJiYKLQkJCQkJCQkJCSBuZXdf
cGF0aC0+cGFyYWxsZWxfc2FmZSA+PSBvbGRfcGF0aC0+cGFyYWxsZWxfc2FmZSkKLQkJCQkJCQkJ
cmVtb3ZlX29sZCA9IHRydWU7CS8qIG5ldyBkb21pbmF0ZXMgb2xkICovCi0JCQkJCQkJZWxzZSBp
ZiAob3V0ZXJjbXAgPT0gQk1TX1NVQlNFVDIgJiYKLQkJCQkJCQkJCSBuZXdfcGF0aC0+cm93cyA+
PSBvbGRfcGF0aC0+cm93cyAmJgotCQkJCQkJCQkJIG5ld19wYXRoLT5wYXJhbGxlbF9zYWZlIDw9
IG9sZF9wYXRoLT5wYXJhbGxlbF9zYWZlKQotCQkJCQkJCQlhY2NlcHRfbmV3ID0gZmFsc2U7IC8q
IG9sZCBkb21pbmF0ZXMgbmV3ICovCiAJCQkJCQkJLyogZWxzZSBkaWZmZXJlbnQgcGFyYW1ldGVy
aXphdGlvbnMsIGtlZXAgYm90aCAqLwogCQkJCQkJfQogCQkJCQkJYnJlYWs7CkBAIC01ODYsOCAr
NTc2LDcgQEAgYWRkX3BhdGgoUmVsT3B0SW5mbyAqcGFyZW50X3JlbCwgUGF0aCAqbmV3X3BhdGgp
CiAJCQkJCQl7CiAJCQkJCQkJb3V0ZXJjbXAgPSBibXNfc3Vic2V0X2NvbXBhcmUoUEFUSF9SRVFf
T1VURVIobmV3X3BhdGgpLAogCQkJCQkJCQkJCQkJCQkgIFBBVEhfUkVRX09VVEVSKG9sZF9wYXRo
KSk7Ci0JCQkJCQkJaWYgKChvdXRlcmNtcCA9PSBCTVNfRVFVQUwgfHwKLQkJCQkJCQkJIG91dGVy
Y21wID09IEJNU19TVUJTRVQxKSAmJgorCQkJCQkJCWlmIChvdXRlcmNtcCA9PSBCTVNfRVFVQUwg
JiYKIAkJCQkJCQkJbmV3X3BhdGgtPnJvd3MgPD0gb2xkX3BhdGgtPnJvd3MgJiYKIAkJCQkJCQkJ
bmV3X3BhdGgtPnBhcmFsbGVsX3NhZmUgPj0gb2xkX3BhdGgtPnBhcmFsbGVsX3NhZmUpCiAJCQkJ
CQkJCXJlbW92ZV9vbGQgPSB0cnVlOwkvKiBuZXcgZG9taW5hdGVzIG9sZCAqLwpAQCAtNTk4LDgg
KzU4Nyw3IEBAIGFkZF9wYXRoKFJlbE9wdEluZm8gKnBhcmVudF9yZWwsIFBhdGggKm5ld19wYXRo
KQogCQkJCQkJewogCQkJCQkJCW91dGVyY21wID0gYm1zX3N1YnNldF9jb21wYXJlKFBBVEhfUkVR
X09VVEVSKG5ld19wYXRoKSwKIAkJCQkJCQkJCQkJCQkJICBQQVRIX1JFUV9PVVRFUihvbGRfcGF0
aCkpOwotCQkJCQkJCWlmICgob3V0ZXJjbXAgPT0gQk1TX0VRVUFMIHx8Ci0JCQkJCQkJCSBvdXRl
cmNtcCA9PSBCTVNfU1VCU0VUMikgJiYKKwkJCQkJCQlpZiAob3V0ZXJjbXAgPT0gQk1TX0VRVUFM
ICYmCiAJCQkJCQkJCW5ld19wYXRoLT5yb3dzID49IG9sZF9wYXRoLT5yb3dzICYmCiAJCQkJCQkJ
CW5ld19wYXRoLT5wYXJhbGxlbF9zYWZlIDw9IG9sZF9wYXRoLT5wYXJhbGxlbF9zYWZlKQogCQkJ
CQkJCQlhY2NlcHRfbmV3ID0gZmFsc2U7IC8qIG9sZCBkb21pbmF0ZXMgbmV3ICovCi0tIAoyLjQz
LjAKCg==
--=_fa8278efaf2b84c81fd938bb8f9e065b--