Re: inherit support for foreign tables

Hello, I tried minimal implementation to do that.

At Tue, 25 Feb 2014 19:45:56 +0900, Etsuro Fujita wrote
> In addition to an issue pointed out recently by Horiguchi-san, I've
> found there is another issue we have to discuss. That is, we can't
> build any parameterized Append paths for an inheritance tree that
> contains at least one foreign table, in set_append_rel_pathlist(). This
> is because the patch doesn't take into consideration
> "reparameterization" for paths for a foreign table, which attempts to
> modify these paths to have greater parameterization so that each child
> path in the inheritance tree can have the exact same parameterization.
> To do so, I think we would need to add code for the foreign-table case
> to reparameterize_path(). And I think we should introduce a new FDW
> routine, say ReparameterizeForeignPath() because the processing would be
> performed best by the FDW itself.
>
> Comments are welcome!

I think the problem is foreign childs in inheritance tables
prevents all menber in the inheritance relation from using
parameterized paths, correct?

|=# explain select * from p join (select uname from c1 limit 1) s on s.uname = p.uname;
| QUERY PLAN
|-------------------------------------------------------------------------------
| Hash Join (cost=0.04..244.10 rows=50 width=58)
| Hash Cond: (p.uname = c1_1.uname)
| -> Append (cost=0.00..206.01 rows=10012 width=50)
| -> Seq Scan on p (cost=0.00..0.00 rows=1 width=168)
| -> Seq Scan on c1 (cost=0.00..204.01 rows=10001 width=50)
| -> Foreign Scan on c2 (cost=0.00..2.00 rows=10 width=168)
| Foreign File: /etc/passwd
| Foreign File Size: 1851
| -> Hash (cost=0.03..0.03 rows=1 width=8)
| -> Limit (cost=0.00..0.02 rows=1 width=8)
| -> Seq Scan on c1 c1_1 (cost=0.00..204.01 rows=10001 width=8)
| Planning time: 1.095 ms

Hmm. I tried minimal implementation to do that. This omits cost
recalculation but seems to work as expected. This seems enough if
cost recalc is trivial here.

diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index b79af7a..18ced04 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -2062,6 +2062,16 @@ reparameterize_path(PlannerInfo *root, Path *path,
case T_SubqueryScan:
return create_subqueryscan_path(root, rel, path->pathkeys,
required_outer);
+ case T_ForeignScan:
+ {
+ ForeignPath *fpath = (ForeignPath*) path;
+ ForeignPath *newpath = makeNode(ForeignPath);
+ memcpy(newpath, fpath, sizeof(ForeignPath));
+ newpath->path.param_info =
+ get_baserel_parampathinfo(root, rel, required_outer);
+ /* cost recalc omitted */
+ return (Path *)newpath;
+ }
default:
break;
}
....

|=# explain select * from p join (select uname from c1 limit 1) s on s.uname = p.uname;
| QUERY PLAN
|----------------------------------------------------------------------------
| Nested Loop (cost=0.00..10.46 rows=50 width=58)
| -> Limit (cost=0.00..0.02 rows=1 width=8)
| -> Seq Scan on c1 c1_1 (cost=0.00..204.01 rows=10001 width=8)
| -> Append (cost=0.00..10.30 rows=12 width=158)
| -> Seq Scan on p (cost=0.00..0.00 rows=1 width=168)
| Filter: (c1_1.uname = uname)
| -> Index Scan using i_c1 on c1 (cost=0.29..8.30 rows=1 width=50)
| Index Cond: (uname = c1_1.uname)
| -> Foreign Scan on c2 (cost=0.00..2.00 rows=10 width=168)
| Filter: (c1_1.uname = uname)
| Foreign File: /etc/passwd
| Foreign File Size: 1851
| Planning time: 2.044 ms

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center