Re: inherit support for foreign tables - Mailing list pgsql-hackers
From | Etsuro Fujita |
---|---|
Subject | Re: inherit support for foreign tables |
Date | |
Msg-id | 532F8EC1.6020401@lab.ntt.co.jp Whole thread Raw |
In response to | Re: inherit support for foreign tables (Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>) |
Responses |
Re: inherit support for foreign tables
|
List | pgsql-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
pgsql-hackers by date: