pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
To: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Cc: shigeru.hanada@gmail.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: inherit support for foreign tables
Date: Mon, 24 Mar 2014 10:47:45 +0900
Message-ID: <532F8EC1.6020401@lab.ntt.co.jp> (raw)
In-Reply-To: <532AE62A.6020307@lab.ntt.co.jp>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>

(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

view thread (179+ messages)  latest in thread

Message-ID: <532F8EC1.6020401@lab.ntt.co.jp>
Permalink:  ../532F8EC1.6020401@lab.ntt.co.jp/
Also on:    postgresql.org/message-id/532F8EC1.6020401@lab.ntt.co.jp

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: fujita.etsuro@lab.ntt.co.jp, horiguchi.kyotaro@lab.ntt.co.jp, shigeru.hanada@gmail.com
  Subject: Re: inherit support for foreign tables
  In-Reply-To: <532F8EC1.6020401@lab.ntt.co.jp>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox