1 摘要
本文首先总结了工作中配置postgres-fdw的关键步骤，然后针对外表查询的几个典型场景，记录了每一种场景下，主节点是如何下推查询到外节点的。
2 环境配置
- 配置两台数据库服务器：node1:5432, node2:6432
- 使用默认数据库: postgres
node1 node2
|--------------------| |-------------------|
| user=user1 --------------------> user=user2 |
| local table: t0 | | |
| foreign table: t1 --------------------> local table: t1 |
| | | |
| foreign table: t2 --------------------> local table: t2 |
|--------------------| |-------------------|
2.1 配置node2:
- node2上创建数据库账号'user2'
- node2上建立两个表: t1, t2
2.1.1 使用超级用户登录node2，在node2上创建数据库账号'user2'
psql -h node2 -p 6432 -d postgres
CREATE ROLE user2 WITH LOGIN PASSWORD 'pwd#@1';
GRANT ALL PRIVILEGES ON DATABASE postgres to user2;
2.1.2 更改node2:pg_hba.conf，允许node1使用账号user2访问node2
- node1的IP地址：192.168.199.110
host postgres user2 192.168.199.110/32 md5
2.1.3 在node2上，以user2身份，创建表: t1, t2
psql -h node2 -p 6432 -d postgres -U user2
create table t1 ( id int, name varchar(100));
create table t2 ( id int, name varchar(100));
2.2 配置node1
- node1: 创建数据库账号'user1'
- node1: 建立一个本地表t0，两个外表: t1, t2，分别指向node2上的t1, t2
2.2.1 使用超级用户，在node1上启用postgres-fdw, 创建数据库账号'user1'
psql -h node1 -p 5432 -d postgres
create extension postgres_fdw;
CREATE ROLE user1 WITH LOGIN PASSWORD '#@1qw';
GRANT ALL PRIVILEGES ON DATABASE postgres to user1;
grant usage on foreign data wrapper postgres_fdw to user1;
2.2.2 node1上，以user1登录
psql -h node1 -p 5432 -d postgres -U user1
2.2.3 node1上,使用user1账号，创建本地表: t0
create table t0 ( id int, name varchar(100));
2.2.4 node1上,使用user1账号，创建外表：t1, t2
;;外表t1,t2
node1.t1 ---> node2.t1
node1.t2 ---> node2.t2
create server fnode2
foreign data wrapper postgres_fdw
options (host 'node2', port '6432', dbname 'postgres');
create user mapping for user1
server fnode2
options (user 'user2', password 'pwd#@1');
create foreign table t1 (
id int,
name varchar(100)
)
server fnode2
options (schema_name 'public', table_name 't1');
create foreign table t2 (
id int,
name varchar(100)
)
server fnode2
options (schema_name 'public', table_name 't2');
2.3 加载数据，
- 通过节点node1，分别向node1的三个表(node1.t0, node1.t1,node1.t2)插入数据
- 三个表的数据相同，每个表有100条数据
insert into t0 select generate_series(1,100), 'tom-'||generate_series(1,100) ;
insert into t1 select generate_series(1,100), 'tom-'||generate_series(1,100) ;
insert into t2 select generate_series(1,100), 'tom-'||generate_series(1,100) ;
三个表的数据相同：
id name
----------------------
1 tom-1
2 tom-2
3 tom-3
... ...
3. 探索外表查询
3.1 node1单外表扫描
- node1.t1是外表
- 通过node1，查询t1
- node1把查询，连带查询条件，推送到node2
node1上执行查询：
psql -h node1 -U user1 -d postgres -c "select * from t1 where id=1;"
node2的执行过程如下：
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DECLARE c1 CURSOR FOR SELECT id, name FROM public.t1 WHERE ((id = 1));
FETCH 100 FROM c1;
CLOSE c1;
COMMIT TRANSACTION;
3.2 node1上：单外表+单本地表join，推送到外表时无过滤条件(where)
- t0是本地表
- t1是外表
在node1上执行查询
psql -h node1 -U user1 -d postgres -c "select * from t0,t1 where t0.id=1 and t0.name=t1.name;"
下推到node2的查询如下：
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DECLARE c1 CURSOR FOR SELECT id, name FROM public.t1;
FETCH 100 FROM c1;
FETCH 100 FROM c1;
CLOSE c1;
COMMIT TRANSACTION;
3.3 node1上：单外表+单本地表join，推送到外表时带有过滤条件(where)
- t0是本地表
- t1是外表
在node1上执行查询:
psql -h node1 -U user1 -d postgres -c "select * from t0,t1 where t1.id=1 and t0.name=t1.name;"
下推到node2的查询如下：
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DECLARE c1 CURSOR FOR
SELECT id, name FROM public.t1 WHERE ((id = 1));
FETCH 100 FROM c1;
CLOSE c1;
COMMIT TRANSACTION;
3.4 node1上，双外表join
- t1是外表
- t2是外表
通过node1执行：
psql -h node1 -U user1 -d postgres -c "select * from t1,t2 where t1.id=1 and t1.name=t2.name;"
下推到node2的查询如下：
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DECLARE c1 CURSOR FOR
SELECT r1.id, r1.name, r2.id, r2.name FROM (public.t1 r1 INNER JOIN public.t2 r2 ON (((r1.name = r2.name)) AND ((r1.id = 1))));
FETCH 100 FROM c1;
CLOSE c1;
COMMIT TRANSACTION;
3.5 node1上，对外表avg()
- t1是外表
通过node1执行：
psql -h node1 -U user1 -d postgres -c "select avg(id) from t1 where id<100;"
下推到node2的查询如下：
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DECLARE c1 CURSOR FOR
SELECT avg(id) FROM public.t1 WHERE ((id < 100));
FETCH 100 FROM c1;
CLOSE c1;
COMMIT TRANSACTION;
3.6 node1上，对外表sort
- t1是外表
通过node1执行：
psql -h node1 -U user1 -d postgres -c "select * from t1 where id<100 order by name;"
下推到node2的查询如下：
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DECLARE c1 CURSOR FOR
SELECT id, name FROM public.t1 WHERE ((id < 100)) ORDER BY name ASC NULLS LAST;
FETCH 100 FROM c1;
CLOSE c1;
COMMIT TRANSACTION;