Hello,
> 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?
regards,
====
### on pg1/pg2:
create table pu1 (a int not null, b int not null, c int, d text);
create unique index i_pu1_ab on pu1 (a, b);
create unique index i_pu1_c on pu1 (c);
create table cu11 (like pu1 including all) inherits (pu1);
create table cu12 (like pu1 including all) inherits (pu1);
insert into cu11 (select a / 5, 4 - (a % 5), a, 'cu11' from
generate_series(000000, 099999) a);
insert into cu12 (select a / 5, 4 - (a % 5), a, 'cu12' from
generate_series(100000, 199999) a);
### on pg1:
create extension postgres_fdw;
create server pg2 foreign data wrapper postgres_fdw options (host '/tmp', port
'5433', dbname 'postgres');
create user mapping for current_user server pg2 options (user 'horiguti');
create foreign table _cu11 (a int, b int, c int, d text) server pg2 options
(table_name 'cu11', use_remote_estimate 'true');
create foreign table _cu12 (a int, b int, c int, d text) server pg2 options
(table_name 'cu12', use_remote_estimate 'true');
create table rpu1 (a int, b int, c int, d text);
alter foreign table _cu11 inherit rpu1;
alter foreign table _cu12 inherit rpu1;
analyze rpu1;
=# explain analyze select pu1.*
from pu1 join rpu1 on (pu1.c = rpu1.c) where pu1.a = 3;
QUERY PLAN
-------------------------------------------------------------------------------
Nested Loop (cost=0.00..2414.57 rows=11 width=19)
(actual time=0.710..7.167 rows=5 loops=1)
-> Append (cost=0.00..30.76 rows=11 width=19)
<local side.. ommitted>
-> Append (cost=0.00..216.68 rows=3 width=4)
(actual time=0.726..1.419 rows=1 loops=5)
-> Seq Scan on rpu1 (cost=0.00..0.00 rows=1 width=4)
(actual time=0.000..0.000 rows=0 loops=5)
Filter: (pu1.c = c)
-> Foreign Scan on _cu11 (cost=100.30..108.34 rows=1 width=4)
(actual time=0.602..0.603 rows=1 loops=5)
-> Foreign Scan on _cu12 (cost=100.30..108.34 rows=1 width=4)
(actual time=0.566..0.566 rows=0 loops=5)
Planning time: 4.452 ms
Total runtime: 7.663 ms
(15 rows)
--
Kyotaro Horiguchi
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
