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

(2014/03/18 18:38), Kyotaro HORIGUCHI wrote:
>> By the way, Can I have a simple script to build an environment to
>> run this on?
>
> I built test environment and ran the simple test using
> postgres_fdw and got parameterized path from v3 patch on the
> following operation as shown there, and v6 also gives one, but I
> haven't seen the reparameterization of v6 patch work.
>
> # How could I think to have got it work before?
>
> Do you have any idea to make postgreReparameterizeForeignPath on
> foreign (child) tables works effectively?

> =# explain analyze select pu1.*
>      from pu1 join rpu1 on (pu1.c = rpu1.c) where pu1.a = 3;

ISTM postgresReparameterizeForeignPath() cannot be called in this query
in principle.  Here is a simple example for the case where the
use_remote_estimate option is true:

# On mydatabase

mydatabase=# CREATE TABLE mytable (id INTEGER, x INTEGER);
CREATE TABLE
mydatabase=# INSERT INTO mytable SELECT x, x FROM generate_series(0,
9999) x;
INSERT 0 10000

# On postgres

postgres=# CREATE TABLE inttable (id INTEGER);
CREATE TABLE
postgres=# INSERT INTO inttable SELECT x FROM generate_series(0, 9999) x;
INSERT 0 10000
postgres=# ANALYZE inttable;
ANALYZE

postgres=# CREATE TABLE patest0 (id INTEGER, x INTEGER);
CREATE TABLE
postgres=# CREATE TABLE patest1 () INHERITS (patest0);
CREATE TABLE
postgres=# INSERT INTO patest1 SELECT x, x FROM generate_series(0, 9999) x;
INSERT 0 10000
postgres=# CREATE INDEX patest1_id_idx ON patest1(id);
CREATE INDEX
postgres=# CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'mydatabase');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR PUBLIC SERVER myserver OPTIONS (user
'pgsql');
CREATE USER MAPPING
postgres=# CREATE FOREIGN TABLE patest2 () INHERITS (patest0) SERVER
myserver OPTIONS (table_name 'mytable');
CREATE FOREIGN TABLE
postgres=# ANALYZE patest0;
ANALYZE
postgres=# ANALYZE patest1;
ANALYZE
postgres=# ANALYZE patest2;
ANALYZE
postgres=# EXPLAIN VERBOSE SELECT * FROM patest0 join (SELECT id FROM
inttable LIMIT 1) ss ON patest0.id = ss.id;
                                            QUERY PLAN
-------------------------------------------------------------------------------------------------
  Nested Loop  (cost=0.00..478.36 rows=2 width=12)
    Output: patest0.id, patest0.x, inttable.id
    ->  Limit  (cost=0.00..0.01 rows=1 width=4)
          Output: inttable.id
          ->  Seq Scan on public.inttable  (cost=0.00..145.00 rows=10000
width=4)
                Output: inttable.id
    ->  Append  (cost=0.00..478.31 rows=3 width=8)
          ->  Seq Scan on public.patest0  (cost=0.00..0.00 rows=1 width=8)
                Output: patest0.id, patest0.x
                Filter: (inttable.id = patest0.id)
          ->  Index Scan using patest1_id_idx on public.patest1
(cost=0.29..8.30 rows=1 width=8)
                Output: patest1.id, patest1.x
                Index Cond: (patest1.id = inttable.id)
          ->  Foreign Scan on public.patest2  (cost=100.00..470.00
rows=1 width=8)
                Output: patest2.id, patest2.x
                Remote SQL: SELECT id, x FROM public.mytable WHERE
(($1::integer = id))
  Planning time: 0.233 ms
(17 rows)

I revised the patch.  Patche attached, though I plan to update the
documentation further early next week.

Thanks,

Best regards,
Etsuro Fujita