近几年XC的快速推进，我和我的团队一直在努力做从 Oracle 迁移到国产数据库的工作， 其中包含国产数据库基于postgreSQL的kingbase/highgo等，还有opengauss等下游发行版产品，因为得于pg的优化器或对oracle的兼容性，在传统企业也广泛应用，企业应用程序像java开发的颇多，而java代码中对于数字的变量赋值的数据类型有多种，在postgresql/openGauss系的数据库与oracle存在差异，可能会导致PostgreSQL JDBC 驱动程序不像 Oracle JDBC 驱动程序那样转换该数据类型。数据类型不匹配的结果最终在 PostgreSQL系中是全表扫描，而不像oracle中的使用索引，导致SQL性能变差，下面做个演示。
在postgresql中数字类型有多种:
numeric (x)
numeric (x,y)
numeric
smallint
bigint
int
float
Oracle测试
Oracle数据库都可以使用上索引,测试用例
SQL> desc datatype_test
Name Null? Type
------------------------------- -------- ----------------------------
1 NUMBER_DECIMAL_VAL NUMBER(12,2)
2 NUMBER_VAL NUMBER(12)
3 RANDOM_VAL NUMBER(4)
SQL> @ind datatype_test
Display indexes where table or index name matches %datatype_test%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
ANBOB DATATYPE_TEST DATATYPE_NUMBER_DECIMAL_VAL 1 NUMBER_DECIMAL_VAL
DATATYPE_NUMBER_VAL 1 NUMBER_VAL
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
ANBOB DATATYPE_TEST DATATYPE_NUMBER_DECIMAL_VAL NORMAL NO VALID NO N 2 21 10000 10000 24 2024-09-07 02:16:10 1 VISIBLE
DATATYPE_TEST DATATYPE_NUMBER_VAL NORMAL NO VALID NO N 2 21 6341 10000 9508 2024-09-07 02:16:10 1 VISIBLE
d:\code> javac -classpath d:\code\ojdbc11.jar;.; oratest.java
d:\code> java -classpath d:\code\ojdbc11.jar;.; oratest
Password:
===== Database info =====
DatabaseProductName: Oracle
DatabaseProductVersion: Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Beta
Version 23.1.0.0.0
DatabaseMajorVersion: 23
DatabaseMinorVersion: 0
===== Driver info =====
DriverName: Oracle JDBC driver
DriverVersion: 23.1.0.0.0
DriverMajorVersion: 23
DriverMinorVersion: 1
===== JDBC/DB attributes =====
Supports getGeneratedKeys(): true
===== Database info =====
===== Query Plan - Cast Int to Numeric =====
Plan hash value: 423740054
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 24 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DATATYPE_TEST | 2 | 24 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DATATYPE_NUMBER_VAL | 2 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NUMBER_VAL"=:1)
===== Query Plan - Cast Long to Numeric =====
Plan hash value: 423740054
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 24 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DATATYPE_TEST | 2 | 24 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DATATYPE_NUMBER_VAL | 2 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NUMBER_VAL"=:1)
===== Query Plan - Cast Float to Numeric =====
Plan hash value: 423740054
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 24 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DATATYPE_TEST | 2 | 24 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DATATYPE_NUMBER_VAL | 2 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NUMBER_VAL"=:1)
===== Query Plan - Cast Double to Numeric =====
Plan hash value: 423740054
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 24 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DATATYPE_TEST | 2 | 24 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DATATYPE_NUMBER_VAL | 2 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NUMBER_VAL"=:1)
=========================
Command successfully executed
openGauss测试
而在openGauss中 setDouble、setFloat并没有使用索引
openGauss=# \d datatype_test
Table "public.datatype_test"
Column | Type | Modifiers
---------------------+---------------+-----------
int_val | integer |
bigint_val | bigint |
numeric_val | numeric(12,0) |
numeric_decimal_val | numeric(12,2) |
smallint_val | smallint |
Indexes:
"datatype_test_bigint" btree (bigint_val) TABLESPACE pg_default
"datatype_test_int" btree (int_val) TABLESPACE pg_default
"datatype_test_numeric" btree (numeric_val) TABLESPACE pg_default
"datatype_test_numeric_dec" btree (numeric_decimal_val) TABLESPACE pg_default
"datatype_test_smallint" btree (smallint_val) TABLESPACE pg_default
openGauss=# select * from datatype_test where rownum<=10;
int_val | bigint_val | numeric_val | numeric_decimal_val | smallint_val
---------+------------+-------------+---------------------+--------------
0 | 0 | 2629571 | 8027495.09 | 21980
1 | 1 | 4410637 | 4965025.11 | 1113
2 | 2 | 3375204 | 7674783.76 | 10370
3 | 3 | 1893231 | 7208360.96 | 13
4 | 4 | 1885063 | 1209501.51 | 2069
5 | 5 | 4820570 | 7902291.24 | 21655
6 | 6 | 6765559 | 2703309.94 | 10375
7 | 7 | 2825079 | 167383.31 | 14019
8 | 8 | 102532 | 7399141.65 | 21817
9 | 9 | 4949097 | 1772420.93 | 5358
(10 rows)
D:\postgresql> javac -classpath d:\postgresql\postgresql-42.2.23.jar pgtest.java
D:\postgresql> java -classpath d:\postgresql\postgresql-42.2.23.jar;.; pgtest
Password:
===== Database info =====
DatabaseProductName: PostgreSQL
DatabaseProductVersion: 9.2.4
DatabaseMajorVersion: 9
DatabaseMinorVersion: 2
===== Driver info =====
DriverName: PostgreSQL JDBC Driver
DriverVersion: 42.2.23
DriverMajorVersion: 42
DriverMinorVersion: 2
===== JDBC/DB attributes =====
Supports getGeneratedKeys(): true
===== Database info =====
Current Date from Postgres : 2024-09-06 17:52:12.715107+08
Client connected pid from Postgres : 140220000237312
Postgres DB Unique Name from Postgres : postgres
Client connected hostname from Postgres : null
Client connected application_name from Postgres : PostgreSQL JDBC Driver
===== Query Plan - Cast Int to Numeric =====
[Bypass]
Index Scan using datatype_test_numeric on datatype_test (cost=0.00..12.28 rows=2 width=28) (actual time=0.019..0.019 rows=0 loops=1)
Index Cond: (numeric_val = 10001::numeric)
Total runtime: 0.057 ms
===== Query Plan - Cast Long to Numeric =====
[Bypass]
Index Scan using datatype_test_numeric on datatype_test (cost=0.00..12.28 rows=2 width=28) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: (numeric_val = 10001::numeric)
Total runtime: 0.044 ms
===== Query Plan - Cast Float to Numeric =====
Seq Scan on datatype_test (cost=0.00..233497.01 rows=50000 width=28) (actual time=1976.746..1976.746 rows=0 loops=1)
Filter: ((numeric_val)::double precision = 10001::real)
Rows Removed by Filter: 10000001
Total runtime: 1976.833 ms
===== Query Plan - Cast Double to Numeric =====
Seq Scan on datatype_test (cost=0.00..233497.01 rows=50000 width=28) (actual time=1841.364..1841.364 rows=0 loops=1)
Filter: ((numeric_val)::double precision = 10001::double precision)
Rows Removed by Filter: 10000001
Total runtime: 1841.417 ms
===== Query Plan - Cast Int to Numeric =====
[Bypass]
Index Scan using datatype_test_numeric on datatype_test (cost=0.00..12.28 rows=2 width=28) (actual time=0.013..0.013 rows=0 loops=1)
Index Cond: (numeric_val = 10001::numeric)
Total runtime: 0.054 ms
=========================
Command successfully executed
改成查询numeric_decimal_val 列
D:\postgresql> javac -classpath d:\postgresql\postgresql-42.2.23.jar pgtest.java
D:\postgresql> java -classpath d:\postgresql\postgresql-42.2.23.jar;.; pgtest
Password:
===== Database info =====
DatabaseProductName: PostgreSQL
DatabaseProductVersion: 9.2.4
DatabaseMajorVersion: 9
DatabaseMinorVersion: 2
===== Driver info =====
DriverName: PostgreSQL JDBC Driver
DriverVersion: 42.2.23
DriverMajorVersion: 42
DriverMinorVersion: 2
===== JDBC/DB attributes =====
Supports getGeneratedKeys(): true
===== Database info =====
Current Date from Postgres : 2024-09-06 17:53:20.336918+08
Client connected pid from Postgres : 140219772106496
Postgres DB Unique Name from Postgres : postgres
Client connected hostname from Postgres : null
Client connected application_name from Postgres : PostgreSQL JDBC Driver
===== Query Plan - Cast Int to Bigint =====
[Bypass]
Index Scan using datatype_test_bigint on datatype_test (cost=0.00..8.27 rows=1 width=28) (actual time=0.161..0.162 rows=1 loops=1)
Index Cond: (bigint_val = 10001)
Total runtime: 0.204 ms
===== Query Plan - Cast Long to Bigint =====
[Bypass]
Index Scan using datatype_test_bigint on datatype_test (cost=0.00..8.27 rows=1 width=28) (actual time=0.013..0.013 rows=1 loops=1)
Index Cond: (bigint_val = 10001::bigint)
Total runtime: 0.037 ms
===== Query Plan - Cast Float to Bigint =====
Seq Scan on datatype_test (cost=0.00..233497.01 rows=50000 width=28) (actual time=1.151..1038.628 rows=1 loops=1)
Filter: ((bigint_val)::double precision = 10001::real)
Rows Removed by Filter: 10000000
Total runtime: 1038.705 ms
===== Query Plan - Cast Double to Bigint =====
Seq Scan on datatype_test (cost=0.00..233497.01 rows=50000 width=28) (actual time=1.074..1011.278 rows=1 loops=1)
Filter: ((bigint_val)::double precision = 10001::double precision)
Rows Removed by Filter: 10000000
Total runtime: 1011.341 ms
===== Query Plan - Cast Int to Bigint =====
[Bypass]
Index Scan using datatype_test_bigint on datatype_test (cost=0.00..8.27 rows=1 width=28) (actual time=0.016..0.016 rows=1 loops=1)
Index Cond: (bigint_val = 10001)
Total runtime: 0.052 ms
Note:
Double 和Float 两个都没有使用索引。
数据库表的字段类型修改为double precision可以解决该问题。
===== Query Plan - Cast Int to Doublepre =====
[Bypass]
Index Scan using idxid4 on test3 (cost=0.00..8.27 rows=1 width=34) (actual time=0.128..0.128 rows=0 loops=1)
Index Cond: (id4 = 10001::double precision)
Total runtime: 0.162 ms
Note:
检索列改为double precision类型后，现在都可以正常使用索引。但是double precision存储上会占用更多的字节长度(8bytes)。
点击查看原文， 阅读测试java源码。