KingabseES kingbase_fdw 跨库关联查询

背景

我们在做综合应用项目的时候，通常会面临客户的每个应用系统使用各自的数据库，或者存放在不同的服务器。查询报表可能使用多个应用数据，这样就需要跨库读取数据表或视图。

KINGBASE_FDW 是一种外部访问接口，它可以被用来访问存储在外部的数据。想要使用fdw访问数据需要先确保：网络通，数据库访问配置(sys_hba.conf)正常，同时远端数据库的用户必须有表的相关权限。

简述KINGBASE_FDW

1. 创建扩展 kingbase_fdw

   create extension kingbase_fdw;
   

2. 创建远程Server

   create server srv_test foreign data wrapper kingbase_fdw options(host '127.0.0.1',port '54321',dbname 'test');
   

3. 创建User maping

   create user mapping for kingbase server srv_test options (user 'kingbase', password '123456');
   

4. 创建外部表

   create foreign table public.test_tab01 (
       id integer ,
       c1 text
       ) server srv_test options (schema_name 'public', table_name 'tab01');
   

5. 测试外部表

   • 读取全部数据

   explain analyze  select * from public.test_tab01;
   
   Foreign Scan on test_tab01  (cost=100.00..150.95 rows=1365 width=36) (actual time=0.230..509.699 rows=1000000 loops=1)
   Planning Time: 0.041 ms
   Execution Time: 532.775 ms
   

   • 支持索引

   explain analyze select * from public.test_tab01 where id = 1000 ;
   
   Foreign Scan on test_tab01  (cost=100.00..150.95 rows=1365 width=36) (actual time=0.230..0.440 rows=1 loops=1)
   Planning Time: 0.041 ms
   Execution Time: 0.485 ms
   

本地与外部表的关联查询

1. 本地表与外部表的关联查询

   首先将外部表的数据缓存到本地，然后与本地表进行hash join。

   explain analyze select * from test02 a join public.tab01_db b on a.id = b.id  where a.id <= 1000;
   
   Hash Join  (cost=153.85..40186.87 rows=1081 width=37) (actual time=0.421..568.423 rows=1000 loops=1)
     Hash Cond: (b.id = a.id)
     ->  Foreign Scan on tab01_db b  (cost=100.00..37508.00 rows=1000000 width=25) (actual time=0.211..512.571 rows=1000000 loops=1)
     ->  Hash  (cost=40.34..40.34 rows=1081 width=12) (actual time=0.202..0.203 rows=1000 loops=1)
           Buckets: 2048  Batches: 1  Memory Usage: 59kB
           ->  Index Scan using test02_pkey on test02 a  (cost=0.42..40.34 rows=1081 width=12) (actual time=0.011..0.120 rows=1000 loops=1)
                 Index Cond: (id <= 1000)
   Planning Time: 0.168 ms
   Execution Time: 568.608 ms
   

2. 使用lateral改写查询
   Nested Loop 虽然没有读取外部表的全部数据，但多次通过session读取外部表，使得执行时间较长。如果loops数值更大，则执行时间远超整体读取数据方式。

   explain analyze select * from test02 a
            join lateral ( select * from public.tab01_db b where a.id = b.id limit all) b on true
   where a.id <= 1000;
   
   Nested Loop  (cost=100.42..21628731.58 rows=1081 width=37) (actual time=0.224..102.295 rows=1000 loops=1)
     ->  Index Scan using test02_pkey on test02 a  (cost=0.42..40.34 rows=1081 width=12) (actual time=0.012..0.354 rows=1000 loops=1)
           Index Cond: (id <= 1000)
     ->  Foreign Scan on tab01_db b  (cost=100.00..20008.02 rows=1 width=25) (actual time=0.084..0.084 rows=1 loops=1000)
   Planning Time: 0.138 ms
   Execution Time: 102.522 ms
   
   

3. 利用数组改写查询
   利用数组，可以一次性读取所需外部表的少量数据，即避免读取多余数据量，又防止多次建立session而产生的执行时长。由于是通过session读取外部表数据，cpu_tuple_cost默认值0.01会影响执行计划的正确性，建议设置cpu_tuple_cost值0.2 以上。

   explain analyze
   with a as (select * from test02 where id <= 1000),
        ids as ( select array(select id from a) ids),
        b as  ( select b.* from tab01_db b, ids where id = any (ids))
   select /*+set(cpu_tuple_cost 0.2)*/ *
   from a, b
   where a.id = b.id;
   
   Hash Join  (cost=813.27..220724.94 rows=1 width=37) (actual time=1.045..2.281 rows=1000 loops=1)
     Hash Cond: (b.id = a.id)
     CTE a
       ->  Index Scan using test02_pkey on test02  (cost=0.42..40.34 rows=1081 width=12) (actual time=0.011..0.122 rows=1000 loops=1)
             Index Cond: (id <= 1000)
     InitPlan 2 (returns $1)
       ->  CTE Scan on a a_1  (cost=0.00..21.62 rows=1081 width=4) (actual time=0.013..0.246 rows=1000 loops=1)
     ->  Foreign Scan on tab01_db b  (cost=100.00..220010.10 rows=10 width=25) (actual time=0.953..2.048 rows=1000 loops=1)
     ->  Hash  (cost=432.40..432.40 rows=1081 width=12) (actual time=0.086..0.086 rows=1000 loops=1)
           Buckets: 2048  Batches: 1  Memory Usage: 59kB
           ->  CTE Scan on a  (cost=0.00..432.40 rows=1081 width=12) (actual time=0.000..0.037 rows=1000 loops=1)
   Planning Time: 0.192 ms
   Execution Time: 2.471 ms
   
   

   总结

   外部表在查询中，属于“黑盒”，所以必须通过lateral、CTE或子查询，使得外部表可以接受过滤条件。

   综合考虑查询脚本的复杂程度，建议需要外部表数据时，极少量则使用lateral，少于30%使用数组方式，多于30%使用整体方式。