Hi Kaigai-san, Hanada-san,
Attached please find a patch to print the column names prefixed by the
relation names. I haven't tested the patch fully. The same changes will be
needed for CustomPlan node specific code.
Now I am able to make sense out of the Output information
postgres=# explain verbose select * from ft1 join ft2 using (val);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
Foreign Scan (cost=100.00..125.60 rows=2560 width=12)
Output: ft1.val, ft1.val2, ft2.val2
Remote SQL: SELECT r.a_0, r.a_1, l.a_1 FROM (SELECT val, val2 FROM
public.lt) l (a_0, a_1) INNER JOIN (SELECT val, val2 FROM public.lt) r
(a_0, a_1)
ON ((r.a_0 = l.a_0))
(3 rows)
On Fri, Mar 6, 2015 at 6:41 AM, Kouhei Kaigai <[email protected]> wrote:
> > Actually val and val2 come from public.lt in "r" side, but as you say
> > it's too difficult to know that from EXPLAIN output. Do you have any
> > idea to make the "Output" item more readable?
> >
> A fundamental reason why we need to have symbolic aliases here is that
> postgres_fdw has remote query in cstring form. It makes implementation
> complicated to deconstruct/construct a query that is once constructed
> on the underlying foreign-path level.
> If ForeignScan keeps items to construct remote query in expression node
> form (and construction of remote query is delayed to beginning of the
> executor, probably), we will be able to construct more human readable
> remote query.
>
> However, I don't recommend to work on this great refactoring stuff
> within the scope of join push-down support project.
>
> Thanks,
> --
> NEC OSS Promotion Center / PG-Strom Project
> KaiGai Kohei <[email protected]>
>
>
> > -----Original Message-----
> > From: [email protected]
> > [mailto:[email protected]] On Behalf Of Shigeru Hanada
> > Sent: Thursday, March 05, 2015 10:00 PM
> > To: Ashutosh Bapat
> > Cc: Kaigai Kouhei(海外 浩平); Robert Haas; PostgreSQL-development
> > Subject: Re: [HACKERS] Join push-down support for foreign tables
> >
> > Hi Ashutosh, thanks for the review.
> >
> > 2015-03-04 19:17 GMT+09:00 Ashutosh Bapat <
> [email protected]>:
> > > In create_foreignscan_path() we have lines like -
> > > 1587 pathnode->path.param_info = get_baserel_parampathinfo(root,
> rel,
> > > 1588
> > > required_outer);
> > > Now, that the same function is being used for creating foreign scan
> paths
> > > for joins, we should be calling get_joinrel_parampathinfo() on a join
> rel
> > > and get_baserel_parampathinfo() on base rel.
> >
> > Got it. Please let me check the difference.
> >
> > >
> > > The patch seems to handle all the restriction clauses in the same way.
> There
> > > are two kinds of restriction clauses - a. join quals (specified using
> ON
> > > clause; optimizer might move them to the other class if that doesn't
> affect
> > > correctness) and b. quals on join relation (specified in the WHERE
> clause,
> > > optimizer might move them to the other class if that doesn't affect
> > > correctness). The quals in "a" are applied while the join is being
> computed
> > > whereas those in "b" are applied after the join is computed. For
> example,
> > > postgres=# select * from lt;
> > > val | val2
> > > -----+------
> > > 1 | 2
> > > 1 | 3
> > > (2 rows)
> > >
> > > postgres=# select * from lt2;
> > > val | val2
> > > -----+------
> > > 1 | 2
> > > (1 row)
> > >
> > > postgres=# select * from lt left join lt2 on (lt.val2 = lt2.val2);
> > > val | val2 | val | val2
> > > -----+------+-----+------
> > > 1 | 2 | 1 | 2
> > > 1 | 3 | |
> > > (2 rows)
> > >
> > > postgres=# select * from lt left join lt2 on (true) where (lt.val2 =
> > > lt2.val2);
> > > val | val2 | val | val2
> > > -----+------+-----+------
> > > 1 | 2 | 1 | 2
> > > (1 row)
> > >
> > > The difference between these two kinds is evident in case of outer
> joins,
> > > for inner join optimizer puts all of them in class "b". The remote
> query
> > > sent to the foreign server has all those in ON clause. Consider foreign
> > > tables ft1 and ft2 pointing to local tables on the same server.
> > > postgres=# \d ft1
> > > Foreign table "public.ft1"
> > > Column | Type | Modifiers | FDW Options
> > > --------+---------+-----------+-------------
> > > val | integer | |
> > > val2 | integer | |
> > > Server: loopback
> > > FDW Options: (table_name 'lt')
> > >
> > > postgres=# \d ft2
> > > Foreign table "public.ft2"
> > > Column | Type | Modifiers | FDW Options
> > > --------+---------+-----------+-------------
> > > val | integer | |
> > > val2 | integer | |
> > > Server: loopback
> > > FDW Options: (table_name 'lt2')
> > >
> > > postgres=# explain verbose select * from ft1 left join ft2 on
> (ft1.val2 =
> > > ft2.val2) where ft1.val + ft2.val > ft1.val2 or ft2.val is null;
> > >
> > > QUERY PLAN
> > >
> > >
> >
> ----------------------------------------------------------------------------
> >
> ---------------------------------------------------------------------------
> > >
> >
> ----------------------------------------------------------------------------
> > --------
> > > Foreign Scan (cost=100.00..125.60 rows=2560 width=16)
> > > Output: val, val2, val, val2
> > > Remote SQL: SELECT r.a_0, r.a_1, l.a_0, l.a_1 FROM (SELECT val,
> val2 FROM
> > > public.lt2) l (a_0, a_1) RIGHT JOIN (SELECT val, val2 FROM public.lt)
> r (a
> > > _0, a_1) ON ((((r.a_0 + l.a_0) > r.a_1) OR (l.a_0 IS NULL))) AND
> ((r.a_1 =
> > > l.a_1))
> > > (3 rows)
> > >
> > > The result is then wrong
> > > postgres=# select * from ft1 left join ft2 on (ft1.val2 = ft2.val2)
> where
> > > ft1.val + ft2.val > ft1.val2 or ft2.val is null;
> > > val | val2 | val | val2
> > > -----+------+-----+------
> > > 1 | 2 | |
> > > 1 | 3 | |
> > > (2 rows)
> > >
> > > which should match the result obtained by substituting local tables for
> > > foreign ones
> > > postgres=# select * from lt left join lt2 on (lt.val2 = lt2.val2) where
> > > lt.val + lt2.val > lt.val2 or lt2.val is null;
> > > val | val2 | val | val2
> > > -----+------+-----+------
> > > 1 | 3 | |
> > > (1 row)
> > >
> > > Once we start distinguishing the two kinds of quals, there is some
> > > optimization possible. For pushing down a join it's essential that all
> the
> > > quals in "a" are safe to be pushed down. But a join can be pushed
> down, even
> > > if quals in "a" are not safe to be pushed down. But more clauses one
> pushed
> > > down to foreign server, lesser are the rows fetched from the foreign
> server.
> > > In postgresGetForeignJoinPath, instead of checking all the restriction
> > > clauses to be safe to be pushed down, we need to check only those
> which are
> > > join quals (class "a").
> >
> > The argument restrictlist of GetForeignJoinPaths contains both
> > conditions mixed, so I added extract_actual_join_clauses() to separate
> > it into two lists, join_quals and other clauses. This is similar to
> > what create_nestloop_plan and siblings do.
> >
> >
> > >
> > > Following EXPLAIN output seems to be confusing
> > > ft1 and ft2 both are pointing to same lt on a foreign server.
> > > postgres=# explain verbose select ft1.val + ft1.val2 from ft1, ft2
> where
> > > ft1.val + ft1.val2 = ft2.val;
> > >
> > > QUERY PLAN
> > >
> > >
> >
> ----------------------------------------------------------------------------
> >
> ---------------------------------------------------------------------------
> > > --------------------------
> > > Foreign Scan (cost=100.00..132.00 rows=2560 width=8)
> > > Output: (val + val2)
> > > Remote SQL: SELECT r.a_0, r.a_1 FROM (SELECT val, NULL FROM
> public.lt) l
> > > (a_0, a_1) INNER JOIN (SELECT val, val2 FROM public.lt) r (a_0, a_1)
> ON ((
> > > (r.a_0 + r.a_1) = l.a_0))
> > >
> > > Output just specified val + val2, it doesn't tell, where those val and
> val2
> > > come from, neither it's evident from the rest of the context.
> > >
> >
> > Actually val and val2 come from public.lt in "r" side, but as you say
> > it's too difficult to know that from EXPLAIN output. Do you have any
> > idea to make the "Output" item more readable?
> >
> > --
> > Shigeru HANADA
> >
> >
> > --
> > Sent via pgsql-hackers mailing list ([email protected])
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
>
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 9281874..840890e 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -723,29 +723,37 @@ ExplainPreScanNode(PlanState *planstate, Bitmapset **rels_used)
case T_SeqScan:
case T_IndexScan:
case T_IndexOnlyScan:
case T_BitmapHeapScan:
case T_TidScan:
case T_SubqueryScan:
case T_FunctionScan:
case T_ValuesScan:
case T_CteScan:
case T_WorkTableScan:
- case T_ForeignScan:
case T_CustomScan:
*rels_used = bms_add_member(*rels_used,
((Scan *) plan)->scanrelid);
break;
case T_ModifyTable:
*rels_used = bms_add_member(*rels_used,
((ModifyTable *) plan)->nominalRelation);
break;
+ case T_ForeignScan:
+ {
+ ForeignScan *foreign_scan = (ForeignScan *)plan;
+ if (foreign_scan->scan.scanrelid != 0)
+ *rels_used = bms_add_member(*rels_used, foreign_scan->scan.scanrelid);
+ else
+ *rels_used = bms_add_members(*rels_used, foreign_scan->relids);
+ }
+ break;
default:
break;
}
/* initPlan-s */
if (planstate->initPlan)
ExplainPreScanSubPlans(planstate->initPlan, rels_used);
/* lefttree */
if (outerPlanState(planstate))
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index b4b2dc5..0a9bf37 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -2008,20 +2008,23 @@ create_foreignscan_plan(PlannerInfo *root, ForeignPath *best_path,
foreach (lc, scan_plan->fdw_ps_tlist)
{
TargetEntry *tle = lfirst(lc);
if (tle->resjunk)
found_resjunk = true;
else if (found_resjunk)
elog(ERROR, "junk TLE should not apper prior to valid one");
}
+
+ /* Set the relids that are represented by this foreign scan for Explain */
+ scan_plan->relids = best_path->path.parent->relids;
}
/* Copy cost data from Path to Plan; no need to make FDW do this */
copy_path_costsize(&scan_plan->scan.plan, &best_path->path);
/* Track FDW server-id; no need to make FDW do this */
scan_plan->fdw_handler = rel->fdw_handler;
/*
* Replace any outer-relation variables with nestloop params in the qual
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 213034b..ba278de 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -484,20 +484,23 @@ typedef struct WorkTableScan
* ----------------
*/
typedef struct ForeignScan
{
Scan scan;
Oid fdw_handler; /* OID of FDW handler */
List *fdw_exprs; /* expressions that FDW may evaluate */
List *fdw_ps_tlist; /* optional pseudo-scan tlist for FDW */
List *fdw_private; /* private data for FDW */
bool fsSystemCol; /* true if any "system column" is needed */
+ Bitmapset *relids; /* When scan.scanrelid is 0, the list of
+ * relations represented by this node
+ */
} ForeignScan;
/* ----------------
* CustomScan node
*
* The comments for ForeignScan's fdw_exprs, fdw_varmap and fdw_private fields
* apply equally to custom_exprs, custom_ps_tlist and custom_private.
* Note that since Plan trees can be copied, custom scan providers *must*
* fit all plan data they need into those fields; embedding CustomScan in
* a larger struct will not work.
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
