Re: inherit support for foreign tables - Mailing list pgsql-hackers

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