背景

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

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 selectfrom test02 a
   joinlateral ( selectfrom 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 (selectfrom 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%使用整体方式。