Oracle迁移openGauss/PostgreSQL注意事项:java代码中的setDouble、setFloat会导致全表扫描
近几年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?
目前这篇文章还没有评论(Rss)