pg连接外部数据库的TDS_FDW 之后

pg通过tds_fdw实现外部表后，一般就可以是同本地表进行操作。

CREATE FOREIGN TABLE "test"."Remote_IncomeStatement" (
  "VAJ47" timestamp(6),
  "BCK01B" int4,
  "BBY01" int4,
  "VAJ25" numeric(18,4),
  "VAJ36" money
)
SERVER "HIS_SQLServer"
OPTIONS ("query" 'select  VAJ47,BCK01B  ,BBY01,VAJ25,VAJ36 from vaj1"')
;

ALTER FOREIGN TABLE "test"."Remote_IncomeStatement" 
  OWNER TO "postgres";

COMMENT ON COLUMN "test"."Remote_IncomeStatement"."VAJ47" IS 'RIS_Date';

COMMENT ON COLUMN "test"."Remote_IncomeStatement"."BCK01B" IS 'RIS_DeptCode';

COMMENT ON COLUMN "test"."Remote_IncomeStatement"."BBY01" IS 'RIS_ItemCode';

COMMENT ON COLUMN "test"."Remote_IncomeStatement"."VAJ25" IS 'RIS_SL';

COMMENT ON COLUMN "test"."Remote_IncomeStatement"."VAJ36" IS 'RIS_JE';

 

注意点：不要使用外库的视图作为外部表。因为这个速度相当慢。

如果对外部表进行相关连接查询，建议通过物化视图来实现。

CREATE MATERIALIZED VIEW "test"."MV_IncomeStatement"
AS
SELECT "Remote_IncomeStatement"."VAJ47",
    "Remote_IncomeStatement"."BCK01B",
    "Remote_IncomeStatement"."BBY01",
    "Remote_IncomeStatement"."VAJ25",
    "Remote_IncomeStatement"."VAJ36"
   FROM test."Remote_IncomeStatement";

ALTER MATERIALIZED VIEW "test"."MV_IncomeStatement" OWNER TO "postgres";

 

为什么？还是因为速度

直接使用外部表实现	物化视图实现
SELECT a.*,b."PID_ServiceName" FROM "test"."Remote_IncomeStatement" a LEFT JOIN "public"."PayItems_Dict" b ON a."BCK01B"::TEXT=b."PID_PayCode" where to_char(a."VAJ47", 'yyyy-mm') ='2021-06'	SELECT a.*,b."PID_ServiceName" FROM "test"."MV_IncomeStatement" a LEFT JOIN "public"."PayItems_Dict" b ON a."BCK01B"::TEXT=b."PID_PayCode" where to_char(a."VAJ47", 'yyyy-mm') ='2021-06'
共300条记录 运行时间 0.440s	共300条记录 运行时间 0.005s
	运行时间：0.35s　　REFRESH MATERIALIZED VIEW "test"."MV_IncomeStatement"

 

 

 

 

 

 

注意物化视图数据不是最新的。需要最新数据要执行：REFRESH MATERIALIZED VIEW "test"."MV_IncomeStatement"; 运行时间：0.35s。

效率还是非常客观的。