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