Re: inherit support for foreign tables

From: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
Cc: shigeru(dot)hanada(at)gmail(dot)com, pgsql-hackers(at)postgresql(dot)org
Subject: Re: inherit support for foreign tables
Date: 2014-03-20 12:59:22
Message-ID: 532AE62A.6020307@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

(2014/03/18 18:38), Kyotaro HORIGUCHI wrote:
>> By the way, Can I have a simple script to build an environment to
>> run this on?
>
> I built test environment and ran the simple test using
> postgres_fdw and got parameterized path from v3 patch on the
> following operation as shown there, and v6 also gives one, but I
> haven't seen the reparameterization of v6 patch work.
>
> # How could I think to have got it work before?
>
> Do you have any idea to make postgreReparameterizeForeignPath on
> foreign (child) tables works effectively?

> =# explain analyze select pu1.*
> from pu1 join rpu1 on (pu1.c = rpu1.c) where pu1.a = 3;

ISTM postgresReparameterizeForeignPath() cannot be called in this query
in principle. Here is a simple example for the case where the
use_remote_estimate option is true:

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

I revised the patch. Patche attached, though I plan to update the
documentation further early next week.

Thanks,

Best regards,
Etsuro Fujita

Attachment Content-Type Size
foreign_inherit-v7.patch text/plain 47.3 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2014-03-20 13:35:38 Re: Portability issues in shm_mq
Previous Message Alexander Korotkov 2014-03-20 12:32:18 Re: jsonb and nested hstore