PostgreSQL使用oracle_fdw与Oracle查询性能对比

在PostgreSQL中使用oracle_fdw

安装oracle_fdw插件

  1. 安装前要求：
  • 全部功能要求PostgreSQL9.3及以上
  • Oracle client要求10.1以上
  • 确保PostgreSQL配置了--without-ldap
  • 确保pg_config在PATH中(用 pg_config --pgxs测试)
  • 设置了ORACLE_HOME环境变量

下载oracle_fdw源代码,
并解压到某个目录下，进入文件目录，执行：

1
2
$ make
$ make install

安装完毕后，使用psql连接到PostgreSQL；

1
$ psql -d testdb;

使用PostgreSQL的超级用户配置oralce_fdw:

1
2
3
testdb=# create extension oracle_fdw;
testdb=# create server oracle_121_56 foreign data wrapper oracle_fdw options (dbserver '//192.168.121.56:1521/orcl');
testdb=# create user mapping for public server oracle_121_56 options (user 'dbuser' password 'test');

简单查询，无索引

生成测试数据，创建一个表，并同时添加1百万条数据

1
create table test_table as select rownum as id,to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,trunc(dbms_random.value(0, 100)) as random_id,dbms_random.string('x', 20) random_string from dual connect by level <= 1000000;

创建完表后，在原来表的基础上追加记录

1
2
insert into test_table (ID, INC_DATETIME,RANDOM_ID,RANDOM_STRING) select rownum as id,  to_char(sysdate + rownum, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,trunc(dbms_random.value(0, 100)) as random_id, dbms_random.string('x', 20) random_string  from dual  connect by level <= 1000000;
commit;

上面SQL是利用了Oracle数据库语法的几个实用小技巧实现的:

  1. 利用Oracle特有的“connect by”树形连接语法生成测试记录，“level <= 10”表示要生成10记录；

  2. 利用rownum虚拟列生成递增的整数数据；

  3. 利用sysdate函数加一些简单运算来生成日期数据，本例中是每条记录的时间加1秒；

  4. 利用dbms_random.value函数生成随机的数值型数据，本例中是生成0到100之间的随机整数；

  5. 利用dbms_random.string函数生成随机的字符型数据，本例中是生成长度为20的随机字符串，字符串中可以包括字符或数字。

  6. to_char(sysdate + rownum, ‘yyyy-mm-dd hh24:mi:ss’) 这里是转换为字符串，如果该字段的类型为TimeStamp时间戳，那这里可以改写一下方法，转换为时间戳 to_timestamp(sysdate + rownum, ‘yyyy-mm-dd hh24:mi:ss’)

PostgreSQL 创建相应的外部表

1
testdb=# create foreign table test_table(id int, inc_datetime varchar(19), random_id int, random_string varchar(4000)) server oracle_121_56 options (schema 'DBUSER', table 'TEST_TABLE');
  1. 查询表的行数
  • sqlplus
1
$ sqlplus dbuser/test@//192.168.121.56:1521/orcl
1
2
3
4
5
SQL> select count(*) from test_table;
 COUNT(*)
----------
2000000
Elapsed: 00:00:00.13
  • oracle_fdw
1
2
3
4
5
6
testdb=# select count(*) from test_table;
count
---------
2000000
(1 row)
Time: 5264.299 ms (00:05.264)

查看oracle session中真正执行的sql语句，红框为oracle_fdw执行时，oracle session中执行的sql：

在psql中查看select count(*) from test_table;的执行计划：

可见oracle处理的sql即是查询计划中的oracle query后面的sql

查看网络流量

oracle_fdw的网络流量明显比sqlplus要高出很多。

  1. 查询某条记录
  • sqlplus
1
2
3
4
5
6
7
SQL> select * from test_table where id= 50;

	ID  	INC_DATETIME 	  RANDOM_ID		RANDOM_STRING
-------- -------------------- ------------ -----------------------------
	50  2018-07-25 01:13:38	       21	2VMPGE0K6Y293T9WRT7G
	50  2018-09-13 01:49:31	       51	RGUV1BJJNSTNXDMXMVRL
Elapsed: 00:00:00.05
  • oracle_fdw
1
2
3
4
5
6
7
8
testdb=# select * from test_table where id= 50;
 id |    inc_datetime     | random_id |    random_string
----+---------------------+-----------+----------------------
 50 | 2018-07-25 01:13:38 |        21 | 2VMPGE0K6Y293T9WRT7G
 50 | 2018-09-13 01:49:31 |        51 | RGUV1BJJNSTNXDMXMVRL
(2 rows)

Time: 56.811 ms
  1. 使用orderby id获取前5行记录
  • sqlplus
1
2
3
4
5
6
7
8
9
10
11
SQL> select * from test_table order by id offset 0 rows fetch next 5 rows only;

	ID  INC_DATETIME 		RANDOM_ID	RANDOM_STRING
-------- ------------------- ---------- ----------------------
	 1  2018-07-25 01:12:49	       17	5SPF1INUG6S2L1ZSTU7R
	 1  2018-07-26 01:49:31	       63	BIO3ZOV104VLNKZWGDZN
	 2  2018-07-25 01:12:50	       95	HJFAWH9L7SF85YSPNDHV
	 2  2018-07-27 01:49:31	       86	2PNXP02ZYWYYUZQFWVVN
	 3  2018-07-25 01:12:51	       86	T3UQBBMP3O02LGO6ONPF

Elapsed: 00:00:00.32
  • oracle_fdw
1
2
3
4
5
6
7
8
9
10
11
testdb=# select * from test_table order by id offset 0 rows fetch next 5 rows only;
 id |    inc_datetime     | random_id |    random_string
----+---------------------+-----------+----------------------
  1 | 2018-07-25 01:12:49 |        17 | 5SPF1INUG6S2L1ZSTU7R
  1 | 2018-07-26 01:49:31 |        63 | BIO3ZOV104VLNKZWGDZN
  2 | 2018-07-27 01:49:31 |        86 | 2PNXP02ZYWYYUZQFWVVN
  2 | 2018-07-25 01:12:50 |        95 | HJFAWH9L7SF85YSPNDHV
  3 | 2018-07-25 01:12:51 |        86 | T3UQBBMP3O02LGO6ONPF
(5 rows)

Time: 1169.874 ms (00:01.170)
  1. 使用orderby random_string获取前5行记录
  • sqlplus
1
2
3
4
5
6
7
8
9
10
11
select * from test_table order by random_string offset 0 rows fetch next 5 rows only;

	ID INC_DATETIME 	RANDOM_ID	RANDOM_STRING
---------- ------------------- ---------- --------------------------------
    938145 2018-08-04 21:48:33	       86	00008E7XPHROOHEPUYRC
    686337 2018-08-01 23:51:45	       31	00008JW9S5PXE19F4W4M
     19954 2073-03-12 01:49:31	       69	0000G3A2KNDLVN61WOSH
    382404 3065-07-20 01:49:31	       41	0000JDLZP2J8VZFOJ44J
    234567 2660-10-14 01:49:31	       78	000142EWU0B3GMIPA2AL

Elapsed: 00:00:00.33
  • oracle_fdw
1
2
3
4
5
6
7
8
9
10
11
testdb=# select * from test_table order by random_string offset 0 rows fetch next 5 rows only;
   id   |    inc_datetime     | random_id |    random_string
--------+---------------------+-----------+----------------------
 938145 | 2018-08-04 21:48:33 |        86 | 00008E7XPHROOHEPUYRC
 686337 | 2018-08-01 23:51:45 |        31 | 00008JW9S5PXE19F4W4M
  19954 | 2073-03-12 01:49:31 |        69 | 0000G3A2KNDLVN61WOSH
 382404 | 3065-07-20 01:49:31 |        41 | 0000JDLZP2J8VZFOJ44J
 234567 | 2660-10-14 01:49:31 |        78 | 000142EWU0B3GMIPA2AL
(5 rows)

Time: 13275.162 ms (00:13.275)

oracle_fdw执行时,oracle session中执行的sql

psql中查看执行计划

网络流量对比

简单查询，带索引

创建id索引

1
create index test_id on test_table(id);
  1. 查询某条记录
  • sqlplus
1
2
3
4
5
6
SQL> select * from test_table where id = 8222;
	ID 		INC_DATETIME 	RANDOM_ID	RANDOM_STRING
---------- ------------------- ---------- -----------------------------
      8222 2018-07-25 03:29:50	       68	0VJJSTBAN6RAINSTWQAB
      8222 2041-01-27 01:49:31	       42	3SAYJV50LNDHURP5FU0K
Elapsed: 00:00:00.07
  • oracle_fdw
1
2
3
4
5
6
7
testdb=# select * from test_table where id = 8222;
  id  |    inc_datetime     | random_id |    random_string
------+---------------------+-----------+----------------------
 8222 | 2018-07-25 03:29:50 |        68 | 0VJJSTBAN6RAINSTWQAB
 8222 | 2041-01-27 01:49:31 |        42 | 3SAYJV50LNDHURP5FU0K
(2 rows)
Time: 6.565 ms
  1. 使用orderby id获取前5行记录
  • sqlplus
1
2
3
4
5
6
7
8
9
10
SQL> select * from test_table order by id offset 0 rows fetch next 5 rows only;

	ID INC_DATETIME 	RANDOM_ID	RANDOM_STRING
---------- ------------------- ---------- ----------------------------
	 1 2018-07-25 01:12:49	       17	5SPF1INUG6S2L1ZSTU7R
	 1 2018-07-26 01:49:31	       63	BIO3ZOV104VLNKZWGDZN
	 2 2018-07-25 01:12:50	       95	HJFAWH9L7SF85YSPNDHV
	 2 2018-07-27 01:49:31	       86	2PNXP02ZYWYYUZQFWVVN
	 3 2018-07-25 01:12:51	       86	T3UQBBMP3O02LGO6ONPF
Elapsed: 00:00:00.33
  • oracle_fdw
1
2
3
4
5
6
7
8
9
10
testdb=# select * from test_table order by id offset 0 rows fetch next 5 rows only;
 id |    inc_datetime     | random_id |    random_string
----+---------------------+-----------+----------------------
  1 | 2018-07-25 01:12:49 |        17 | 5SPF1INUG6S2L1ZSTU7R
  1 | 2018-07-26 01:49:31 |        63 | BIO3ZOV104VLNKZWGDZN
  2 | 2018-07-27 01:49:31 |        86 | 2PNXP02ZYWYYUZQFWVVN
  2 | 2018-07-25 01:12:50 |        95 | HJFAWH9L7SF85YSPNDHV
  3 | 2018-07-25 01:12:51 |        86 | T3UQBBMP3O02LGO6ONPF
(5 rows)
Time: 1345.988 ms (00:01.346)

创建random_string索引

1
create index test_string on test_table(random_string);
  1. 查询某条记录
  • sqlplus
1
2
3
4
5
6
SQL> select * from test_table where random_string='GMX0VD9WY4YPN8T4XDML';

	ID INC_DATETIME 	RANDOM_ID	RANDOM_STRING
---------- ------------------- ---------- -------------------------------
       871 2018-07-25 01:27:19	       45	GMX0VD9WY4YPN8T4XDML
Elapsed: 00:00:00.01
  • oracle_fdw
1
2
3
4
5
6
7
testdb=# select * from test_table where random_string='GMX0VD9WY4YPN8T4XDML';
 id  |    inc_datetime     | random_id |    random_string
-----+---------------------+-----------+----------------------
 871 | 2018-07-25 01:27:19 |        45 | GMX0VD9WY4YPN8T4XDML
(1 row)

Time: 118.376 ms
  1. 使用order by random_string获取前5条记录
  • sqlplus
1
2
3
4
5
6
7
8
9
10
SQL> select * from test_table order by random_string offset 0 rows fetch next 5 rows only;

	ID INC_DATETIME 	RANDOM_ID	RANDOM_STRING
---------- ------------------- ---------- ----------------------------------
    938145 2018-08-04 21:48:33	       86	00008E7XPHROOHEPUYRC
    686337 2018-08-01 23:51:45	       31	00008JW9S5PXE19F4W4M
     19954 2073-03-12 01:49:31	       69	0000G3A2KNDLVN61WOSH
    382404 3065-07-20 01:49:31	       41	0000JDLZP2J8VZFOJ44J
    234567 2660-10-14 01:49:31	       78	000142EWU0B3GMIPA2AL
Elapsed: 00:00:00.36
  • oracle_fdw
1
2
3
4
5
6
7
8
9
10
testdb=# select * from test_table order by random_string offset 0 rows fetch next 5 rows only;
   id   |    inc_datetime     | random_id |    random_string
--------+---------------------+-----------+----------------------
 938145 | 2018-08-04 21:48:33 |        86 | 00008E7XPHROOHEPUYRC
 686337 | 2018-08-01 23:51:45 |        31 | 00008JW9S5PXE19F4W4M
  19954 | 2073-03-12 01:49:31 |        69 | 0000G3A2KNDLVN61WOSH
 382404 | 3065-07-20 01:49:31 |        41 | 0000JDLZP2J8VZFOJ44J
 234567 | 2660-10-14 01:49:31 |        78 | 000142EWU0B3GMIPA2AL
(5 rows)
Time: 12848.595 ms (00:12.849)

创建random_id,random_string复合索引

1
create index test_id_string on test_table(random_id,random_string);
  1. 查询某条记录
  • sqlplus
1
2
3
4
5
6
SQL> select * from test_table where random_id=74 and random_string='6PP2R3M0K4ARK411VFC3';

	ID INC_DATETIME 	RANDOM_ID	RANDOM_STRING
---------- ------------------- ---------- -------------------------------------
      4067 2018-07-25 02:20:35	       74	6PP2R3M0K4ARK411VFC3
Elapsed: 00:00:00.00
  • oracle_fdw
1
2
3
4
5
6
7
testdb=# select * from test_table where random_id=74 and random_string='6PP2R3M0K4ARK411VFC3';
  id  |    inc_datetime     | random_id |    random_string
------+---------------------+-----------+----------------------
 4067 | 2018-07-25 02:20:35 |        74 | 6PP2R3M0K4ARK411VFC3
(1 row)

Time: 10.488 ms
  1. orderby
  • sqlplus
1
2
3
4
5
6
7
8
9
SQL> select * from test_table order by random_id,random_string offset 0 rows fetch next 5 rows only;
	ID INC_DATETIME 	RANDOM_ID	RANDOM_STRING
---------- ------------------- ---------- ------------------------------
    231792 2018-07-27 17:36:00		0	000LENLB2SJ8M8AZKTOD
    691626 2018-08-02 01:19:54		0	003GODBKGMRMZC3LTK8Z
    582719 3613-12-29 01:49:31		0	0052V1U6439AGAAAW3QZ
    762898 2018-08-02 21:07:46		0	0081R0VHHHJTRTPTL8RG
    141007 2404-08-17 01:49:31		0	008PFJM81W6NR83WFNXQ
Elapsed: 00:00:00.33
  • oracle_fdw
1
2
3
4
5
6
7
8
9
10
11
testdb=# select * from test_table order by random_id,random_string offset 0 rows fetch next 5 rows only;
   id   |    inc_datetime     | random_id |    random_string
--------+---------------------+-----------+----------------------
 231792 | 2018-07-27 17:36:00 |         0 | 000LENLB2SJ8M8AZKTOD
 691626 | 2018-08-02 01:19:54 |         0 | 003GODBKGMRMZC3LTK8Z
 582719 | 3613-12-29 01:49:31 |         0 | 0052V1U6439AGAAAW3QZ
 762898 | 2018-08-02 21:07:46 |         0 | 0081R0VHHHJTRTPTL8RG
 141007 | 2404-08-17 01:49:31 |         0 | 008PFJM81W6NR83WFNXQ
(5 rows)

Time: 12857.763 ms (00:12.858)

关联查询

  • sqlplus
1
2
3
4
5
6
7
select a.user_name,b.fee from users a,phone_fee b where a.user_id=b.user_id and a.user_id=4999998;

USER_NAME						  FEE
-------------------------------------------------- ----------
C58IV8Z0HKYO					       297.78

Elapsed: 00:00:00.27
  • oracle_fdw
1
2
3
4
5
6
7
testdb=# select a.user_name,b.fee from users a,phone_fee b where a.user_id=b.user_id and a.user_id=4999998;
  user_name   |  fee
--------------+--------
 C58IV8Z0HKYO | 297.78
(1 row)

Time: 139.633 ms

psql的执行计划

1
2
3
4
5
6
7
8
9
10
11
12
13
14
testdb=# explain select a.user_name,b.fee from users a,phone_fee b where a.user_id=b.user_id and a.user_id=4999998;
                                                                         QUERY PLAN

-----------------------------------------------------------------------------------------------
-------------------------------------------------------------
 Nested Loop  (cost=20000.00..52502.50 rows=1000000 width=134)
   ->  Foreign Scan on users a  (cost=10000.00..20000.00 rows=1000 width=134)
         Oracle query: SELECT /*f121f12a8cbc233ce7f3a1c56794af92*/ r1."USER_ID", r1."USER_NAME"
 FROM "DBUSER"."USERS" r1 WHERE (r1."USER_ID" = 4999998)
   ->  Materialize  (cost=10000.00..20005.00 rows=1000 width=32)
         ->  Foreign Scan on phone_fee b  (cost=10000.00..20000.00 rows=1000 width=32)
               Oracle query: SELECT /*d1bc90927d98c625ddda179fef4df315*/ r2."USER_ID", r2."FEE"
 FROM "DBUSER"."PHONE_FEE" r2 WHERE (r2."USER_ID" = 4999998)
(6 rows)

orderby

  • sqlplus
1
2
3
4
5
6
7
8
9
10
11
SQL> select users.user_id,phone_fee.fee,phone_fee.fee_date from users ,phone_fee where users.user_id=phone_fee.user_id order by users.user_id desc  offset 0 rows fetch next 5 rows only;

   USER_ID	  FEE FEE_DATE
---------- ---------- ---------
   4999998     297.78 30-APR-15
   4999997     243.36 31-MAY-15
   4999996	69.79 29-JAN-12
   4999992	91.03 07-OCT-17
   4999992     268.93 01-FEB-15

Elapsed: 00:00:00.71
  • oracle_fdw
1
2
3
4
5
6
7
8
9
10
11
testdb=# select users.user_id,phone_fee.fee,phone_fee.fee_date from users ,phone_fee where users.user_id=phone_fee.user_id order by users.user_id desc  offset 0 rows fetch next 5 rows only;
 user_id |  fee   |  fee_date
---------+--------+------------
 4999998 | 297.78 | 2015-04-30
 4999997 | 243.36 | 2015-05-31
 4999996 |  69.79 | 2012-01-29
 4999992 |  91.03 | 2017-10-07
 4999992 | 268.93 | 2015-02-01
(5 rows)

Time: 27477.661 ms (00:27.478)

psql的执行计划

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
testdb=# explain select users.user_id,phone_fee.fee,phone_fee.fee_date from users ,phone_fee where users.user_id=phone_fee.user_id order by users.user_id desc  offset 0 rows fetch next 5 rows only;

   QUERY PLAN

-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
----------------
 Limit  (cost=20016.61..20016.62 rows=5 width=36)
   ->  Sort  (cost=20016.61..20019.11 rows=1000 width=36)
         Sort Key: users.user_id DESC
         ->  Foreign Scan  (cost=10000.00..20000.00 rows=1000 width=36)
               Oracle query: SELECT /*c6de86645f2edcce90957e3283ade08d*/ r1."USER_ID", r2."FEE"
, r2."FEE_DATE" FROM ("DBUSER"."USERS" r1 INNER JOIN "DBUSER"."PHONE_FEE" r2 ON (r1."USER_ID" =
 r2."USER_ID"))
(5 rows)

oracle session中执行的sql

流量情况

可见oracle_fdw实质上发送的是连接sql，拿到oracle返回的结果，再进行排序的。因此中间消耗的时间里，网络传输数据的消耗是占很大部分的。

结论

oracle_fdw的查询效率主要受发送给oracle的sql查询结果集的数据量影响的，结果集越大，网络IO消耗越多，对查询的影响越大。