Re: inherit support for foreign tables

(2014/03/20 21:59), Etsuro Fujita wrote:
> Here is a simple example for the case where the 
> use_remote_estimate option is true:

Sorry, I incorrectly wrote it.  The following example is for the case
where the option is *false*, as you see.

> # 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)

Sorry for the delay.

Best regards,
Etsuro Fujita