近几年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 两个都没有使用索引。
java for oracle测试代码
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.*;
/*
*
CREATE TABLE datatype_test (
number_decimal_val number(12,2),
number_val number(12),
random_val number(4))
TABLESPACE USERS;
CREATE SEQUENCE datatype_test_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
BEGIN
FOR i in 1 .. 10000
LOOP
INSERT INTO datatype_test VALUES (
datatype_test_seq.nextval,
floor(dbms_random.value(1, 10000)),
floor(dbms_random.value(1, 1000)));
END LOOP;
END;
/
CREATE INDEX datatype_number_decimal_val on datatype_test(number_decimal_val);
CREATE INDEX datatype_number_val on datatype_test(number_val);
*/
public class oratest
{
public static void main(String args[]) throws SQLException, ClassNotFoundException
{
try
{
java.io.Console console = System.console();
Boolean dataTypeCheck = true;
String sourceDatatType = "Numeric";
String inputPassword = new String(console.readPassword("Password: "));
Integer intQueryParam = 10001;
Long longQueryParam = 10001L;
Float floatQueryParam = 10001f;
Double doubleQueryParam = 10001.0;
/**Set URL of Oracle database server*/
String url = "jdbc:oracle:thin:@//172.20.22.166:1521/test";
String xPlanSql = "select * from table(dbms_xplan.display)";
/** properties for creating connection to Oracle database */
Properties props = new Properties();
props.setProperty("user", "anbob");
props.setProperty("password", inputPassword);
/** creating connection to Oracle database using JDBC*/
Connection conn = DriverManager.getConnection(url,props);
DatabaseMetaData dbmd = conn.getMetaData();
System.out.println("===== Database info =====");
System.out.println(" DatabaseProductName: " + dbmd.getDatabaseProductName() );
System.out.println(" DatabaseProductVersion: " + dbmd.getDatabaseProductVersion() );
System.out.println(" DatabaseMajorVersion: " + dbmd.getDatabaseMajorVersion() );
System.out.println(" DatabaseMinorVersion: " + dbmd.getDatabaseMinorVersion() );
System.out.println("===== Driver info =====");
System.out.println(" DriverName: " + dbmd.getDriverName() );
System.out.println(" DriverVersion: " + dbmd.getDriverVersion() );
System.out.println(" DriverMajorVersion: " + dbmd.getDriverMajorVersion() );
System.out.println(" DriverMinorVersion: " + dbmd.getDriverMinorVersion() );
System.out.println("===== JDBC/DB attributes =====");
if (dbmd.supportsGetGeneratedKeys() )
System.out.println(" Supports getGeneratedKeys(): true");
else
System.out.println(" Supports getGeneratedKeys(): false");
System.out.println("===== Database info =====");
String sql = "with session_data as (";
sql = sql + "select sysdate as current_day,SYS_CONTEXT ('USERENV', 'DB_UNIQUE_NAME') as db_name,SYS_CONTEXT ('USERENV', 'SERVICE_NAME') as service_name, ";
sql = sql + "SYS_CONTEXT ('USERENV', 'HOST') as host, SYS_CONTEXT ('USERENV', 'IP_ADDRESS') as ip_address, SYS_CONTEXT('USERENV','SID') sid from dual) ";
sql = sql + "select sd.current_day, sd.db_name, sd.service_name, sd.host, sd.ip_address, ";
sql = sql + "sd.sid, nvl(sci.network_service_banner, 'Traffic Not Encrypted') network_service_banner ";
sql = sql + "from session_data sd ";
sql = sql + "left join v$session_connect_info sci on (sd.sid = sci.sid) ";
sql = sql + "where sci.network_service_banner like '%Crypto-checksumming service adapter%'";
/** creating PreparedStatement object to execute query*/
PreparedStatement preStatement = conn.prepareStatement(sql);
ResultSet result = preStatement.executeQuery();
while(result.next())
{
System.out.println("Current Date from Oracle : " + result.getString("current_day"));
System.out.println("Oracle DB Unique Name from Oracle : " + result.getString("db_name"));
System.out.println("Oracle Connected Listener Service Name from Oracle : " + result.getString("service_name"));
System.out.println("Client connected hostname from Oracle : " + result.getString("host"));
System.out.println("Client connected ip_address from Oracle : " + result.getString("ip_address"));
System.out.println("Client connected encryption info from Oracle : " + result.getString("network_service_banner"));
}
if (dataTypeCheck)
if (sourceDatatType == "Numeric122") {
sql = "EXPLAIN PLAN FOR ";
sql = sql + "select * from datatype_test where number_decimal_val = ?";
} else if (sourceDatatType == "Numeric") {
sql = "EXPLAIN PLAN FOR ";
sql = sql + "select * from datatype_test where number_val = ?";
}
System.out.println("");
System.out.println("");
System.out.println("===== Query Plan - Cast Int to "+ sourceDatatType +" =====");
/** creating PreparedStatement object to execute query*/
preStatement = conn.prepareStatement(sql);
preStatement.setInt(1, intQueryParam);
result = preStatement.executeQuery();
PreparedStatement xPlanStatement = conn.prepareStatement(xPlanSql);
ResultSet xPlanResult = xPlanStatement.executeQuery();
while(xPlanResult.next())
{
System.out.println(" " + xPlanResult.getString(1));
}
System.out.println("");
System.out.println("");
System.out.println("===== Query Plan - Cast Long to "+ sourceDatatType +" =====");
preStatement.setLong(1, longQueryParam);
result = preStatement.executeQuery();
xPlanStatement = conn.prepareStatement(xPlanSql);
xPlanResult = xPlanStatement.executeQuery();
while(xPlanResult.next())
{
System.out.println(" " + xPlanResult.getString(1));
}
System.out.println("");
System.out.println("");
System.out.println("===== Query Plan - Cast Float to "+ sourceDatatType +" =====");
preStatement.setFloat(1, floatQueryParam);
result = preStatement.executeQuery();
xPlanStatement = conn.prepareStatement(xPlanSql);
xPlanResult = xPlanStatement.executeQuery();
while(xPlanResult.next())
{
System.out.println(" " + xPlanResult.getString(1));
}
System.out.println("");
System.out.println("");
System.out.println("===== Query Plan - Cast Double to "+ sourceDatatType +" =====");
preStatement.setDouble(1, doubleQueryParam);
result = preStatement.executeQuery();
xPlanStatement = conn.prepareStatement(xPlanSql);
xPlanResult = xPlanStatement.executeQuery();
while(xPlanResult.next())
{
System.out.println(" " + xPlanResult.getString(1));
}
System.out.println("");
System.out.println("");
conn.close();
System.out.println("=========================");
System.out.println("Command successfully executed");
}
catch(SQLException exp) {
System.out.println("Exception: " + exp.getMessage());
System.out.println("SQL State: " + exp.getSQLState());
System.out.println("Vendor Error: " + exp.getErrorCode());
}
}
}
java for openGauss测试代码
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.*;
/**
CREATE TABLE datatype_test (
int_val int,
bigint_val bigint,
numeric_val numeric(12),
numeric_decimal_val numeric(12,2),
smallint_val smallint);
INSERT INTO datatype_test VALUES (
generate_series(0,10000000),
generate_series(0,10000000),
floor(random()*10000000),
random()*10000000,
floor(random()* (32765-1 + 1) + 1) );
SET SESSION max_parallel_maintenance_workers TO 4;
SET SESSION maintenance_work_mem TO '2 GB';
CREATE INDEX datatype_test_int on datatype_test(int_val);
CREATE INDEX datatype_test_bigint on datatype_test(bigint_val);
CREATE INDEX datatype_test_numeric on datatype_test(numeric_val);
CREATE INDEX datatype_test_numeric_dec on datatype_test(numeric_decimal_val);
CREATE INDEX datatype_test_smallint on datatype_test(smallint_val);
**/
public class pgtest
{
public static void main(String args[]) throws SQLException, ClassNotFoundException
{
try
{
java.io.Console console = System.console();
Boolean dataTypeCheck = true;
String sourceDatatType = "Numeric122";
String inputPassword = new String(console.readPassword("Password: "));
Integer intQueryParam = 10001;
Long longQueryParam = 10001L;
Float floatQueryParam = 10001f;
Double doubleQueryParam = 10001.0;
/**Set URL of Postgres database server*/
String url = "jdbc:postgresql://192.168.56.100:5432/postgres";
/** properties for creating connection to Postgres database */
Properties props = new Properties();
props.setProperty("user", "anbob");
props.setProperty("password", inputPassword);
/** creating connection to Postgres database using JDBC*/
Connection conn = DriverManager.getConnection(url,props);
DatabaseMetaData dbmd = conn.getMetaData();
System.out.println("===== Database info =====");
System.out.println(" DatabaseProductName: " + dbmd.getDatabaseProductName() );
System.out.println(" DatabaseProductVersion: " + dbmd.getDatabaseProductVersion() );
System.out.println(" DatabaseMajorVersion: " + dbmd.getDatabaseMajorVersion() );
System.out.println(" DatabaseMinorVersion: " + dbmd.getDatabaseMinorVersion() );
System.out.println("===== Driver info =====");
System.out.println(" DriverName: " + dbmd.getDriverName() );
System.out.println(" DriverVersion: " + dbmd.getDriverVersion() );
System.out.println(" DriverMajorVersion: " + dbmd.getDriverMajorVersion() );
System.out.println(" DriverMinorVersion: " + dbmd.getDriverMinorVersion() );
System.out.println("===== JDBC/DB attributes =====");
if (dbmd.supportsGetGeneratedKeys() )
System.out.println(" Supports getGeneratedKeys(): true");
else
System.out.println(" Supports getGeneratedKeys(): false");
System.out.println("===== Database info =====");
String sql = "select now() as current_day,current_database() as db_name, ";
sql = sql + "client_hostname as host, application_name, pid from pg_stat_activity ";
sql = sql + " where pid = pg_backend_pid() ";
/** creating PreparedStatement object to execute query*/
PreparedStatement preStatement = conn.prepareStatement(sql);
ResultSet result = preStatement.executeQuery();
while(result.next())
{
System.out.println(" Current Date from Postgres : " + result.getString("current_day"));
System.out.println(" Client connected pid from Postgres : " + result.getString("pid"));
System.out.println(" Postgres DB Unique Name from Postgres : " + result.getString("db_name"));
System.out.println(" Client connected hostname from Postgres : " + result.getString("host"));
System.out.println(" Client connected application_name from Postgres : " + result.getString("application_name"));
}
if (dataTypeCheck)
if (sourceDatatType == "Int") {
sql = "EXPLAIN (ANALYZE, COSTS)";
sql = sql + "select * from datatype_test where int_val = ?";
} else if (sourceDatatType == "Bigint") {
sql = "EXPLAIN (ANALYZE, COSTS)";
sql = sql + "select * from datatype_test where bigint_val = ?";
} else if (sourceDatatType == "Numeric") {
sql = "EXPLAIN (ANALYZE, COSTS)";
sql = sql + "select * from datatype_test where numeric_val = ?";
} else if (sourceDatatType == "Numeric122") {
sql = "EXPLAIN (ANALYZE, COSTS)";
sql = sql + "select * from datatype_test where numeric_decimal_val = ?";
} else if (sourceDatatType == "Smallint") {
sql = "EXPLAIN (ANALYZE, COSTS)";
sql = sql + "select * from datatype_test where smallint_val = ?";
}
Statement stmt = conn.createStatement();
/*-- stmt.execute("SET max_parallel_workers_per_gather = 0"); */
System.out.println("");
System.out.println("");
System.out.println("===== Query Plan - Cast Int to "+ sourceDatatType +" =====");
/** creating PreparedStatement object to execute query*/
preStatement = conn.prepareStatement(sql);
preStatement.setInt(1, intQueryParam);
result = preStatement.executeQuery();
while(result.next())
{
System.out.println(" " + result.getString(1));
}
System.out.println("");
System.out.println("");
System.out.println("===== Query Plan - Cast Long to "+ sourceDatatType +" =====");
preStatement.setLong(1, longQueryParam);
result = preStatement.executeQuery();
while(result.next())
{
System.out.println(" " + result.getString(1));
}
System.out.println("");
System.out.println("");
System.out.println("===== Query Plan - Cast Float to "+ sourceDatatType +" =====");
preStatement.setFloat(1, floatQueryParam);
result = preStatement.executeQuery();
while(result.next())
{
System.out.println(" " + result.getString(1));
}
System.out.println("");
System.out.println("");
System.out.println("===== Query Plan - Cast Double to "+ sourceDatatType +" =====");
preStatement.setDouble(1, doubleQueryParam);
result = preStatement.executeQuery();
while(result.next())
{
System.out.println(" " + result.getString(1));
}
System.out.println("");
System.out.println("");
System.out.println("===== Query Plan - Cast Int to "+ sourceDatatType +" =====");
preStatement.setInt(1, intQueryParam);
result = preStatement.executeQuery();
while(result.next())
{
System.out.println(" " + result.getString(1));
}
System.out.println("");
System.out.println("");
conn.close();
System.out.println("=========================");
System.out.println("Command successfully executed");
}
catch(SQLException exp) {
System.out.println("Exception: " + exp.getMessage());
System.out.println("SQL State: " + exp.getSQLState());
System.out.println("Vendor Error: " + exp.getErrorCode());
}
}
}
数据库表的字段类型修改为double precision可以解决该问题。
openGauss=# \d test3
Table "public.test3"
Column | Type | Modifiers
--------+------------------+-----------
id | integer |
id1 | numeric |
id2 | bigint |
id4 | double precision |
id5 | numeric(10,2) |
Indexes:
"idxid4" btree (id4) TABLESPACE pg_default
openGauss=# insert into test3
select x,x,x,random()*x,random()*x from generate_series(0,100000) as x;openGauss-#
INSERT 0 100001
openGauss=# explain select * from test3 where id4=19;
QUERY PLAN
------------------------------------------------------------------------
Bitmap Heap Scan on test3 (cost=12.12..811.97 rows=500 width=68)
Recheck Cond: (id4 = 19::double precision)
-> Bitmap Index Scan on idxid4 (cost=0.00..12.00 rows=500 width=0)
Index Cond: (id4 = 19::double precision)
(4 rows)
稍微改一下上面的java 代码
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.*;
public class pgtest1
{
public static void main(String args[]) throws SQLException, ClassNotFoundException
{
try
{
java.io.Console console = System.console();
Boolean dataTypeCheck = true;
String sourceDatatType = "Doublepre";
String inputPassword = new String(console.readPassword("Password: "));
Integer intQueryParam = 10001;
Long longQueryParam = 10001L;
Float floatQueryParam = 10001f;
Double doubleQueryParam = 10001.0;
/**Set URL of Postgres database server*/
String url = "jdbc:postgresql://192.168.56.100:5432/postgres";
/** properties for creating connection to Postgres database */
Properties props = new Properties();
props.setProperty("user", "anbob");
props.setProperty("password", inputPassword);
/** creating connection to Postgres database using JDBC*/
Connection conn = DriverManager.getConnection(url,props);
DatabaseMetaData dbmd = conn.getMetaData();
if (dbmd.supportsGetGeneratedKeys() )
System.out.println(" Supports getGeneratedKeys(): true");
else
System.out.println(" Supports getGeneratedKeys(): false");
System.out.println("===== Database info =====");
String sql = "select version()";
/** creating PreparedStatement object to execute query*/
PreparedStatement preStatement = conn.prepareStatement(sql);
ResultSet result = preStatement.executeQuery();
if (dataTypeCheck)
if (sourceDatatType == "Doublepre") {
sql = "EXPLAIN (ANALYZE, COSTS)";
sql = sql + "select * from test3 where id4 = ?";
}
Statement stmt = conn.createStatement();
/*-- stmt.execute("SET max_parallel_workers_per_gather = 0"); */
System.out.println("");
System.out.println("");
System.out.println("===== Query Plan - Cast Int to "+ sourceDatatType +" =====");
/** creating PreparedStatement object to execute query*/
preStatement = conn.prepareStatement(sql);
preStatement.setInt(1, intQueryParam);
result = preStatement.executeQuery();
while(result.next())
{
System.out.println(" " + result.getString(1));
}
System.out.println("");
System.out.println("");
System.out.println("===== Query Plan - Cast Long to "+ sourceDatatType +" =====");
preStatement.setLong(1, longQueryParam);
result = preStatement.executeQuery();
while(result.next())
{
System.out.println(" " + result.getString(1));
}
System.out.println("");
System.out.println("");
System.out.println("===== Query Plan - Cast Float to "+ sourceDatatType +" =====");
preStatement.setFloat(1, floatQueryParam);
result = preStatement.executeQuery();
while(result.next())
{
System.out.println(" " + result.getString(1));
}
System.out.println("");
System.out.println("");
System.out.println("===== Query Plan - Cast Double to "+ sourceDatatType +" =====");
preStatement.setDouble(1, doubleQueryParam);
result = preStatement.executeQuery();
while(result.next())
{
System.out.println(" " + result.getString(1));
}
System.out.println("");
System.out.println("");
System.out.println("===== Query Plan - Cast Int to "+ sourceDatatType +" =====");
preStatement.setInt(1, intQueryParam);
result = preStatement.executeQuery();
while(result.next())
{
System.out.println(" " + result.getString(1));
}
System.out.println("");
System.out.println("");
conn.close();
System.out.println("=========================");
System.out.println("Command successfully executed");
}
catch(SQLException exp) {
System.out.println("Exception: " + exp.getMessage());
System.out.println("SQL State: " + exp.getSQLState());
System.out.println("Vendor Error: " + exp.getErrorCode());
}
}
}
编译
D:\postgresql>javac -classpath d:\postgresql\postgresql-42.2.23.jar pgtest1.java
D:\postgresql>java -classpath d:\postgresql\postgresql-42.2.23.jar;.; pgtest1
Password:
Supports getGeneratedKeys(): true
===== Database info =====
===== 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
===== Query Plan - Cast Long to Doublepre =====
[Bypass]
Index Scan using idxid4 on test3 (cost=0.00..8.27 rows=1 width=34) (actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (id4 = 10001::double precision)
Total runtime: 0.026 ms
===== Query Plan - Cast Float to Doublepre =====
[Bypass]
Index Scan using idxid4 on test3 (cost=0.00..8.27 rows=1 width=34) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (id4 = 10001::real)
Total runtime: 0.029 ms
===== Query Plan - Cast Double to Doublepre =====
[Bypass]
Index Scan using idxid4 on test3 (cost=0.00..8.27 rows=1 width=34) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: (id4 = 10001::double precision)
Total runtime: 0.024 ms
===== 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.007..0.007 rows=0 loops=1)
Index Cond: (id4 = 10001::double precision)
Total runtime: 0.027 ms
=========================
Command successfully executed
Note:
检索列改为double precision类型后，现在都可以正常的使用索引。但是double precision存储上会占用更多的字节长度(8bytes)。
— over —
References:
Why is My App Table Scanning in PostgreSQL but not Oracle?