Oracle迁移openGauss/PostgreSQL注意事项：java代码中的setDouble、setFloat会导致全表扫描

首页 » PostgreSQL/GaussDB » Oracle迁移openGauss/PostgreSQL注意事项：java代码中的setDouble、setFloat会导致全表扫描

Oracle迁移openGauss/PostgreSQL注意事项：java代码中的setDouble、setFloat会导致全表扫描

2024/09/06
PostgreSQL/GaussDB
11 views
没有评论

近几年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?

打赏

double\float, jdbc

目前这篇文章还没有评论(Rss)

我要评论

上一篇： Migrate oracle to openGauss： cast_to_raw/cast_to_varchar2 & base64_encode/base64_decode functions

下一篇： Kingbase( PostgreSQL) 使用 “ON CONFLICT” /Merge 减少vacuum死元组量

ANBOB™