Ashutosh Bapat писал 2022-01-25 17:08:
This code was written long ago. So I may have some recollection
errors. But AFAIR, the reasons we wanted to avoid repeated
estimation/planning for the same foreign join rel were
1. If use_remote_estimate = true, we fetch EXPLAIN output from the
foreign server for various pathkeys. Fetching EXPLAIN output is
expensive. Irrespective of the join order being considered locally, we
expect the foreign server to give us the same cost since the join is
the same. So we avoid running EXPLAIN again and again.
2. If use_remote_estimate = false, the logic to estimate a foreign
join locally is independent of the join order so should yield same
cost again and again. For some reason that doesn't seem to be the case
here.
Hi.
use_remote_estimate was set to false in our case, and yes, it fixed this
issue.
The problem is that if use_remote_estimate = false, the logic to
estimate a foreign join locally
is not independent from the join order.
In above example, without patch we see plan with cost:
cost=382.31..966.86 rows=2 width=37
If we avoid exiting on (joinrel->fdw_private), we can see in gdb the
following cases, when joining all 3 relations:
case 1:
outerrel:relids (stock, order_line), startup_cost = 100, total_cost =
2415.9200000000001, rel_startup_cost = 0, rel_total_cost = 2315.5,
retrieved_rows = 21
innerrel: relid (district) startup_cost = 100, total_cost =
101.14500000000001, rel_startup_cost = 0, rel_total_cost = 1.125,
retrieved_rows = 1
joinrel: startup_cost = 100, total_cost = 2416.875, retrieved_rows = 2
case 2:
outerrel: relids (district, order_line), startup_cost = 100, total_cost
= 281.41999999999996, rel_total_cost = 180, retrieved_rows = 71
innerrel: relid (stock), startup_cost = 100, total_cost =
683.28500000000008, rel_startup_cost = 0, rel_total_cost = 576.625,
retrieved_rows = 333
joinrel: startup_cost = 100, total_cost = 974.88, retrieved_rows = 2
So, (stock join order_line) join district has different cost from
(district join order_line) join stock.
On Tue, Jan 25, 2022 at 1:26 PM Alexander Pyhalov
<a.pyha...@postgrespro.ru> wrote:
It is surprising that the planning time halves with the patch. I
expected it to increase slightly since we will compute estimates
thrice instead of once.
I wouldn't look at estimate times here precisely (and would looked at
costs). Real example where we found it had 100 times more data, but
effect was the same. Here some differences in planing time could be
related to restarting instances with or without patches.
What is use_remote_estimate? Is it ON/OFF?
Yes, it was off.
If we want to proceed along this line, we should take care not to fire
more EXPLAIN queries on the foreign server.
You are correct. Fixed patch to avoid extensive join search when
use_remote_estimate is true.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
From 732e89ef198c0bad713b1a18446902b0132aa72c Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Mon, 24 Jan 2022 18:28:12 +0300
Subject: [PATCH] Look through all possible foreign join orders
---
contrib/postgres_fdw/postgres_fdw.c | 50 +++++++++++++++++++++++++----
1 file changed, 44 insertions(+), 6 deletions(-)
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index bf3f3d9e26e..703e5df1753 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -5950,7 +5950,8 @@ postgresGetForeignJoinPaths(PlannerInfo *root,
JoinType jointype,
JoinPathExtraData *extra)
{
- PgFdwRelationInfo *fpinfo;
+ PgFdwRelationInfo *fpinfo,
+ *oldfpinfo;
ForeignPath *joinpath;
double rows;
int width;
@@ -5960,9 +5961,11 @@ postgresGetForeignJoinPaths(PlannerInfo *root,
* EvalPlanQual gets triggered. */
/*
- * Skip if this join combination has been considered already.
+ * Skip if this join combination has been considered already and rejected
+ * or if this join uses remote estimates.
*/
- if (joinrel->fdw_private)
+ oldfpinfo = (PgFdwRelationInfo *) joinrel->fdw_private;
+ if (oldfpinfo && (!oldfpinfo->pushdown_safe || oldfpinfo->use_remote_estimate))
return;
/*
@@ -6002,6 +6005,12 @@ postgresGetForeignJoinPaths(PlannerInfo *root,
epq_path = GetExistingLocalJoinPath(joinrel);
if (!epq_path)
{
+ if (oldfpinfo)
+ {
+ joinrel->fdw_private = oldfpinfo;
+ pfree(fpinfo);
+ }
+
elog(DEBUG3, "could not push down foreign join because a local path suitable for EPQ checks was not found");
return;
}
@@ -6011,6 +6020,12 @@ postgresGetForeignJoinPaths(PlannerInfo *root,
if (!foreign_join_ok(root, joinrel, jointype, outerrel, innerrel, extra))
{
+ if (oldfpinfo)
+ {
+ joinrel->fdw_private = oldfpinfo;
+ pfree(fpinfo);
+ }
+
/* Free path required for EPQ if we copied one; we don't need it now */
if (epq_path)
pfree(epq_path);
@@ -6044,14 +6059,37 @@ postgresGetForeignJoinPaths(PlannerInfo *root,
/* Estimate costs for bare join relation */
estimate_path_cost_size(root, joinrel, NIL, NIL, NULL,
&rows, &width, &startup_cost, &total_cost);
- /* Now update this information in the joinrel */
- joinrel->rows = rows;
- joinrel->reltarget->width = width;
+
fpinfo->rows = rows;
fpinfo->width = width;
fpinfo->startup_cost = startup_cost;
fpinfo->total_cost = total_cost;
+ if (oldfpinfo)
+ {
+ /* If this path is not cheaper, ignore it */
+ if (startup_cost >= oldfpinfo->startup_cost && total_cost >= oldfpinfo->total_cost)
+ {
+ joinrel->fdw_private = oldfpinfo;
+ pfree(fpinfo);
+ return;
+ }
+ else
+ {
+ /*
+ * We don't know which path is selected - old or new one, but
+ * let's be optimistic.
+ */
+ fpinfo->startup_cost = Min(oldfpinfo->startup_cost, startup_cost);
+ fpinfo->total_cost = Min(oldfpinfo->total_cost, total_cost);
+ pfree(oldfpinfo);
+ }
+ }
+
+ /* Now update this information in the joinrel */
+ joinrel->rows = rows;
+ joinrel->reltarget->width = width;
+
/*
* Create a new join path and add it to the joinrel which represents a
* join between foreign tables.
--
2.25.1
