Message-ID: <532F8EC1.6020401@lab.ntt.co.jp>
Date: Mon, 24 Mar 2014 10:47:45 +0900
From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
User-Agent: Mozilla/5.0 (Windows NT 6.0;
 rv:24.0) Gecko/20100101 Thunderbird/24.4.0
MIME-Version: 1.0
To: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
CC: shigeru.hanada@gmail.com, pgsql-hackers@postgresql.org
Subject: Re: inherit support for foreign tables
References: <20140310.172911.79971075.horiguchi.kyotaro@lab.ntt.co.jp>
	<5321ABD2.6000104@lab.ntt.co.jp>
	<20140317.173044.254841983.horiguchi.kyotaro@lab.ntt.co.jp>
 <20140318.183856.121817800.horiguchi.kyotaro@lab.ntt.co.jp>
 <532AE62A.6020307@lab.ntt.co.jp>
In-Reply-To: <532AE62A.6020307@lab.ntt.co.jp>
Content-Type: text/plain; charset=Shift_JIS
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

(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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers