人大金仓KingbaseES 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

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

4. 创建外部表

createforeigntable 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.95rows=1365 width=36) (actual time=0.230..509.699rows=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.95rows=1365 width=36) (actual time=0.230..0.440rows=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.87rows=1081 width=37) (actual time=0.421..568.423rows=1000 loops=1)
  Hash Cond: (b.id = a.id)
  ->Foreign Scan on tab01_db b  (cost=100.00..37508.00rows=1000000 width=25) (actual time=0.211..512.571rows=1000000 loops=1)
  ->  Hash  (cost=40.34..40.34rows=1081 width=12) (actual time=0.202..0.203rows=1000 loops=1)
        Buckets: 2048  Batches: 1  Memory Usage: 59kB
        ->  Index Scan using test02_pkey on test02 a  (cost=0.42..40.34rows=1081 width=12) (actual time=0.011..0.120rows=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
         joinlateral ( select*from public.tab01_db b where a.id = b.id limit all) b ontruewhere a.id <=1000;

Nested Loop  (cost=100.42..21628731.58rows=1081 width=37) (actual time=0.224..102.295rows=1000 loops=1)
  ->  Index Scan using test02_pkey on test02 a  (cost=0.42..40.34rows=1081 width=12) (actual time=0.012..0.354rows=1000 loops=1)
        Index Cond: (id <=1000)
  ->Foreign Scan on tab01_db b  (cost=100.00..20008.02rows=1 width=25) (actual time=0.084..0.084rows=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 ( selectarray(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.94rows=1 width=37) (actual time=1.045..2.281rows=1000 loops=1)
  Hash Cond: (b.id = a.id)
  CTE a
    ->  Index Scan using test02_pkey on test02  (cost=0.42..40.34rows=1081 width=12) (actual time=0.011..0.122rows=1000 loops=1)
          Index Cond: (id <=1000)
  InitPlan 2 (returns $1)
    ->  CTE Scan on a a_1  (cost=0.00..21.62rows=1081 width=4) (actual time=0.013..0.246rows=1000 loops=1)
  ->Foreign Scan on tab01_db b  (cost=100.00..220010.10rows=10 width=25) (actual time=0.953..2.048rows=1000 loops=1)
  ->  Hash  (cost=432.40..432.40rows=1081 width=12) (actual time=0.086..0.086rows=1000 loops=1)
        Buckets: 2048  Batches: 1  Memory Usage: 59kB
        ->  CTE Scan on a  (cost=0.00..432.40rows=1081 width=12) (actual time=0.000..0.037rows=1000 loops=1)
Planning Time: 0.192 ms
Execution Time: 2.471 ms

总结

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

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