postgres.git / summary / log / commit / refs
commit 53e6f51eef55e8e4520901e40b4d143d944358c0
Author: Robert Haas <rhaas@postgresql.org>
Date: Thu Jul 02 19:45:22 2026 +0000
pg_plan_advice: Don't generate FOREIGN_JOIN advice for a single relation.
A foreign scan can target a single relation while still reaching the
fs_relids branch of pgpa_build_scan() -- for example, when postgres_fdw
pushes an aggregate down over one foreign table. In that case, no
advice should be emitted.
Author: Mahendra Singh Thalor <mahi6run@gmail.com>
Co-authored-by: Robert Haas <rhaas@postgresql.org>
Discussion: http://postgr.es/m/CAKYtNAofuAJBz6++SeikpCb=Y=MO1QgEuZNJ+KZOP2johF1r4Q@mail.gmail.com
contrib/pg_plan_advice/Makefile | 4 +-
contrib/pg_plan_advice/meson.build | 5 ++
contrib/pg_plan_advice/pgpa_scan.c | 8 ++-
contrib/pg_plan_advice/t/001_foreign_scan.pl | 83 ++++++++++++++++++++++++++++
4 files changed, 97 insertions(+), 3 deletions(-)
diff --git a/contrib/pg_plan_advice/Makefile b/contrib/pg_plan_advice/Makefile
index d016723794d..c844846dd54 100644
--- a/contrib/pg_plan_advice/Makefile
+++ b/contrib/pg_plan_advice/Makefile
@@ -22,7 +22,9 @@ PGFILEDESC = "pg_plan_advice - help the planner get the right plan"
REGRESS = alternatives gather join_order join_strategy partitionwise \
prepared scan semijoin syntax
-EXTRA_INSTALL = contrib/tsm_system_time
+TAP_TESTS = 1
+
+EXTRA_INSTALL = contrib/tsm_system_time contrib/postgres_fdw
EXTRA_CLEAN = pgpa_parser.h pgpa_parser.c pgpa_scanner.c
diff --git a/contrib/pg_plan_advice/meson.build b/contrib/pg_plan_advice/meson.build
index f2098947b64..bbab676be31 100644
--- a/contrib/pg_plan_advice/meson.build
+++ b/contrib/pg_plan_advice/meson.build
@@ -64,4 +64,9 @@ tests += {
'syntax',
],
},
+ 'tap': {
+ 'tests': [
+ 't/001_foreign_scan.pl',
+ ],
+ },
}
diff --git a/contrib/pg_plan_advice/pgpa_scan.c b/contrib/pg_plan_advice/pgpa_scan.c
index 21b58a0ac42..a7ee09335fd 100644
--- a/contrib/pg_plan_advice/pgpa_scan.c
+++ b/contrib/pg_plan_advice/pgpa_scan.c
@@ -141,9 +141,13 @@ pgpa_build_scan(pgpa_plan_walker_context *walker, Plan *plan,
* If multiple relations are being targeted by a single
* foreign scan, then the foreign join has been pushed to the
* remote side, and we want that to be reflected in the
- * generated advice.
+ * generated advice. We can't emit FOREIGN_JOIN() advice for
+ * a single relation, so treat that case as an ordinary scan.
*/
- strategy = PGPA_SCAN_FOREIGN;
+ if (bms_membership(relids) == BMS_MULTIPLE)
+ strategy = PGPA_SCAN_FOREIGN;
+ else
+ strategy = PGPA_SCAN_ORDINARY;
break;
case T_Append:
diff --git a/contrib/pg_plan_advice/t/001_foreign_scan.pl b/contrib/pg_plan_advice/t/001_foreign_scan.pl
new file mode 100644
index 00000000000..96c7385219d
--- /dev/null
+++ b/contrib/pg_plan_advice/t/001_foreign_scan.pl
@@ -0,0 +1,83 @@
+# Copyright (c) 2021-2026, PostgreSQL Global Development Group
+
+# Verify plan advice for foreign scans.
+
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('node');
+$node->init;
+$node->append_conf('postgresql.conf',
+ "session_preload_libraries = 'pg_plan_advice'");
+$node->start;
+
+my $host = $node->host;
+my $port = $node->port;
+
+$node->safe_psql(
+ 'postgres', qq{
+ CREATE EXTENSION postgres_fdw;
+ CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
+ OPTIONS (host '$host', port '$port', dbname 'postgres');
+ CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
+ CREATE TABLE base_tab (a int);
+ CREATE TABLE base_tab2 (a int);
+ CREATE FOREIGN TABLE ftab (a int)
+ SERVER loopback OPTIONS (table_name 'base_tab');
+ CREATE FOREIGN TABLE ftab2 (a int)
+ SERVER loopback OPTIONS (table_name 'base_tab2');
+});
+
+# Get the generated advice from EXPLAIN output.
+sub extract_generated_advice
+{
+ my ($explain) = @_;
+ my $generated_advice = '';
+ my $collecting = 0;
+ foreach my $line (split /\n/, $explain)
+ {
+ if ($line =~ /Generated Plan Advice:/)
+ {
+ $collecting = 1;
+ next;
+ }
+ if ($collecting)
+ {
+ $line =~ s/^\s+//;
+ $line =~ s/\s+$//;
+ $generated_advice .= ' ' if $generated_advice ne '';
+ $generated_advice .= $line;
+ }
+ }
+ return $generated_advice;
+}
+
+# A pushed-down aggregate over a single foreign table yields a ForeignScan
+# that names exactly one relation. No FOREIGN_JOIN advice should be generated.
+my $agg_explain = $node->safe_psql('postgres',
+ "EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT count(*) FROM ftab;");
+my $agg_pat = qr/\QRelations: Aggregate on (ftab)\E/;
+like($agg_explain, $agg_pat, 'single-table aggregate is pushed down');
+my $agg_advice = extract_generated_advice($agg_explain);
+is($agg_advice, 'NO_GATHER(ftab)', 'advice for single-table aggregate');
+
+# A foreign join should generate FOREIGN_JOIN advice. Here we force this by
+# disabling local join methods.
+my $join_explain = $node->safe_psql(
+ 'postgres', q{
+ SET enable_mergejoin = off;
+ SET enable_hashjoin = off;
+ SET enable_nestloop = off;
+ EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM ftab JOIN ftab2 USING (a);
+});
+my $ja_expected = 'FOREIGN_JOIN((ftab ftab2)) NO_GATHER(ftab ftab2)';
+my $ja_actual = extract_generated_advice($join_explain);
+is($ja_actual, $ja_expected, 'advice for foreign join');
+
+$node->stop;
+
+done_testing();