外部表

来源：互联网发布：认知人工智能编辑：程序博客网时间：2024/05/16 06:58

http://www.postgres.cn/docs/9.4/postgres-fdw.html

1.外部表使用

1) 安装postgres_fdwCREATE EXTENSION postgres_fdw2) 创建一个外部服务器对象create server freeswitch foreign data wrapper postgres_fdw options (host '192.168.4.118',dbname 'freeswitch',port '5432') ;3) 创建用户映射create user mapping FOR postgres server freeswitch options (user 'postgres',password 'sa') ;4) 创建外部表CREATE FOREIGN TABLE t_trialpacerrelease(      serialnumber character varying(40) NOT NULL,      branchcd integer NOT NULL,      branchmanageid character varying(30) NOT NULL,      settype smallint,      staticip character varying(25),      sourcever character varying(25),      flag character varying(4),      releasedate timestamp without time zone,      landercd integer,      landtime timestamp without time zone,      updatorcd integer,      updatetime timestamp without time zone)SERVER freeswitchOPTIONS (schema_name 'public', table_name 't_trialpacerrelease');

2.成本估算

1) 远程服务器扫描外部表的效率2) 网络通信

3.远程查询优化: 减少外部服务器的数据传输量

1) 带where查询条件传到远程服务器2) 不取回与查询结果无关列

0 0