PostgreSql通过创建外部表而非dblink使访问速度提高,优化sql查询速度

resourceregister库

外部表创建

-- 导入插件
create extension postgres_fdw;
-- 创建服务名称为 operation 的服务 host为ip port为端口 dbname为数据库名称
create server operation foreign data wrapper postgres_fdw options (host 'localhost', port '5432', dbname 'operation');
-- 创建 mapping 
create user mapping for public server operation options (user 'postgres', password 'Szrzyj@123');


-- 创建外部表 schema_name为模式 table_name为表名称
create foreign table t_app_navigation (
navid			varchar	(50), 
sysid			varchar	(50),
nav_name		varchar	(200),
nav_code		varchar	(200),
nav_order		int4	,
url				varchar	(500),
pid				varchar	(50),
nav_description	varchar	(500),
nav_icon		text  
) server operation options (schema_name 'public', table_name 't_app_navigation');
-- 创建完成后直接查询查看是否可以查询到 判断创建是否成功
select *from t_app_navigation

create foreign table t_id_re_role_fun (
id	varchar		(50),
role_id	varchar	(50),
fun_id	varchar	(50),
opt_ids	varchar	(500)
) server operation options (schema_name 'public', table_name 't_id_re_role_fun');

create foreign table t_id_re_role_fun (
id	varchar		(50),
role_id	varchar	(50),
fun_id	varchar	(50),
opt_ids	varchar	(500)
) server operation options (schema_name 'public', table_name 't_id_re_role_fun');


create foreign table t_app_system (
id			varchar	(50),
name		varchar	(100),
sys_code	varchar	(50),
sys_order	numeric	,
url			varchar	(500),
remark		varchar	(500),
sys_gradecode	varchar	(255)

) server operation options (schema_name 'public', table_name 't_app_system');



create foreign table t_app_re_nav_fun (
id	varchar	(255),
navid	varchar	(255),
funid	varchar	(255)

) server operation options (schema_name 'public', table_name 't_app_re_nav_fun');


create foreign table t_app_function (
id			varchar	(50),
name		varchar	(200),
fun_code	varchar	(50),
fun_order	numeric	,
url			varchar	(500),
pid			varchar	(50),
opt_ids		varchar	(500),
remark		varchar	(500),
fun_icon	bytea	,
fun_type	varchar	(255)
) server operation options (schema_name 'public', table_name 't_app_function');

view_t_app_navigtion_auth 视图创建

 SELECT t.navid,
    t.sysid,
    t.nav_name,
    t.sys_code,
    t.nav_code,
    t.nav_order,
    t.nav_icon,
    t.nav_description,
    t.url,
    t.pid,
    t.user_id
   FROM ( SELECT n.navid,
            n.sysid,
            n.nav_name,
            s.sys_code,
            n.nav_code,
            n.nav_order,
            n.nav_icon,
            n.nav_description,
                CASE
                    WHEN ((n.url)::text = ''::text) THEN ff.url
                    ELSE n.url
                END AS url,
            n.pid,
            string_agg((r.user_id)::text, ','::text) AS user_id
           FROM (((((t_app_navigation n
             LEFT JOIN t_id_re_role_fun f ON (((n.navid)::text = (f.fun_id)::text)))
             LEFT JOIN t_id_re_user_role r ON (((r.role_id)::text = (f.role_id)::text)))
             LEFT JOIN t_app_system s ON (((s.id)::text = (n.sysid)::text)))
             LEFT JOIN t_app_re_nav_fun rf ON (((rf.navid)::text = (n.navid)::text)))
             LEFT JOIN t_app_function ff ON (((rf.funid)::text = (ff.id)::text)))
          GROUP BY n.navid, n.sysid, n.nav_name, s.sys_code, n.nav_code, n.nav_order, n.nav_description, n.nav_icon, n.url, ff.url, n.pid) t

view_t_app_role_resource 视图创建

 SELECT t.id,
    t.role_id,
    t.data_id,
    t.auth_scope,
    (t.invalid_time)::text AS invalid_time,
    t.user_id
   FROM ( SELECT a.id,
            a.role_id,
            a.data_id,
            a.auth_scope,
            a.invalid_time,
            string_agg((r.user_id)::text, ','::text) AS user_id
           FROM (t_re_role_resource a
             LEFT JOIN t_id_re_user_role r ON (((r.role_id)::text = (a.role_id)::text)))
          GROUP BY a.id, a.role_id, a.data_id, a.auth_scope, a.invalid_time) t

PostgreSQL通过创建外部表而非dblink提升访问速度的原理

1. 连接方式不同

• dblink
  • 每一次查询，都重新建立一次连接到远端数据库。
  • 查询是通过 SQL 字符串发送，返回的是原始文本（text[]数组），需要在本地进行解析和类型转换。
  • 简单说：每查一次，都连接一次+转换一次 → 开销大，速度慢。
• Foreign Table (FDW, Foreign Data Wrapper，例如postgres_fdw)
  • 一次会话只需建立一次连接，可以复用连接池。
  • 查询的结果直接以结构化数据（行/列）返回，本地服务器可以直接使用。
  • 支持更智能的优化（如下推执行Pushdown）。

---

2. 查询优化程度不同（Pushdown能力）

• dblink
  • 查询是以 SQL 字符串形式发出，PostgreSQL无法理解里面的结构，无法优化。
  • 本地无法做到列/行优化，远端执行结果通常是全量返回。
• Foreign Table（FDW）
  • PostgreSQL能够理解表结构和列类型，可以在本地做如下优化：
    • 列裁剪（只查询需要的列）
    • 行过滤下推（WHERE条件直接在远端执行）
    • 聚合下推（SUM、COUNT等在远端完成）
  • 大大减少网络传输量和处理开销，查询速度明显提升。

---

3. 对比表格

特性	dblink	FDW（postgres_fdw）
连接方式	每次查询重新连接	复用连接，连接池机制
查询表达能力	只能发送SQL字符串，Postgres不了解细节	了解表结构，能参与SQL优化
列/行裁剪	本地裁剪，网络传全量	远端裁剪，网络只传需要部分
聚合推送	不支持	支持远端聚合推送
错误处理	SQL字符串级别，错误捕获困难	标准SQL错误处理
性能	慢，开销大	快，传输和执行都优化

---

4. 总结一句话

FDW外部表比dblink快，是因为它

• 复用连接，避免频繁重连，
• 理解表结构，支持SQL优化（Pushdown），
• 减少不必要的数据传输，
• 整体查询像本地表一样智能且高效。