意外な落とし穴！アプリケーション⇒DBデータ型によるパフォーマンス影響

・インデックスが存在しない
・インデックスが無効（INVALID状態）
・例）CREATE CONCURRENT INDEX句が不正停止した
・式に対するインデックスで、対応する関数や演算が定義されていない
・データ型の不一致
・PostgreSQLの統計情報に基づくと、インデックスによる高速化が実施されない
・その種類のインデックスが、SQL内に記述された演算子をサポートしていなかった

  
  create table t (id bigint primary key, val text);
  insert into t select g, 'sampledata' || g from generate_series(1, 2000000) g(i); 
  analyze t;
  alter table t owner to app;
  

   
  alter system set auto_explain.log_min_duration = '30ms';
  alter system set auto_explain.log_analyze = on;
  alter system set auto_explain.log_buffers = on;
  alter system set auto_explain.log_verbose = on;
  alter system set auto_explain.log_timing = on;
  

  
  import java.sql.*;
  import java.math.BigDecimal;
  public class JdbcDataTypeMismatch
  {
     public static void main(String[] args)
     {
       try
       {
         String url      = "jdbc:postgresql://127.0.0.1:4444/edb";
         String user     = "app";
         String password = "abc123";
         BigDecimal max  = new BigDecimal(10000);
         Class.forName("org.postgresql.Driver");
         Connection c  = DriverManager.getConnection(url, user, password);
         c.setAutoCommit(false);
         PreparedStatement ps = c.prepareStatement("select val from t where id = ?");
         for(BigDecimal i = new BigDecimal(0); i.compareTo(max) < 0; i = i.add(new BigDecimal(1))) {
           ps.setBigDecimal(1, i);
           ResultSet rs = ps.executeQuery();
           while (rs.next()) {
           }
           rs.close();
           c.commit();
           if(i.remainder(new BigDecimal(1000)).compareTo(BigDecimal.ZERO) == 0) {
                  System.out.println(i + "/" + max);
           }
         }
       }
       catch(ClassNotFoundException e)
       {
         System.out.println("Class Not Found : " + e.getMessage());
       }
       catch(SQLException e)
       {
         System.out.println("SQL Exception: " + e.getMessage());
       }
     }
  }
  

  
  javac JdbcDataTypeMismatch.java
  java -cp postgresql-42.6.0.jar:. JdbcDataTypeMismatch
  

  
  2023-09-20 11:47:16 CEST LOG:  duration: 410.446 ms  plan:
          Query Text: select val from t where id = $1
          Seq Scan on public.t  (cost=0.00..44608.00 rows=10000 width=17) (actual time=0.121..410.438 rows=1 loops=1)
            Output: val
            Filter: ((t.id)::numeric = '4'::numeric)
            Rows Removed by Filter: 1999999
            Buffers: shared hit=14608
  2023-09-20 11:47:17 CEST LOG:  duration: 469.929 ms  plan:
          Query Text: select val from t where id = $1
          Seq Scan on public.t  (cost=0.00..44608.00 rows=10000 width=17) (actual time=0.019..469.920 rows=1 loops=1)
            Output: val
            Filter: ((t.id)::numeric = '5'::numeric)
            Rows Removed by Filter: 1999999
            Buffers: shared hit=14608
  2023-09-20 11:47:17 CEST LOG:  duration: 459.444 ms  plan:
          Query Text: select val from t where id = $1
          Seq Scan on public.t  (cost=0.00..44608.00 rows=10000 width=17) (actual time=0.018..459.438 rows=1 loops=1)
            Output: val
            Filter: ((t.id)::numeric = '6'::numeric)
            Rows Removed by Filter: 1999999
            Buffers: shared hit=14608
  [..]
  

  
  edb=# select query, calls, mean_exec_time, stddev_exec_time from pg_stat_statements
  where query like 'select val from t where id = %';
                query              | calls |    mean_exec_time    |   stddev_exec_time
  ---------------------------------+-------+----------------------+----------------------
   select val from t where id = $1 | 10000 | 0.016391058100000012 | 0.014163808369450812
  

  
  edb=# explain analyze select val from t where id = 1;
                                                  QUERY PLAN
  -----------------------------------------------------------------------------------------------------------
   Index Scan using t_pkey on t  (cost=0.43..8.45 rows=1 width=17) (actual time=0.028..0.029 rows=1 loops=1)
     Index Cond: (id = 1)
   Planning Time: 0.203 ms
   Execution Time: 0.051 ms
  (4 rows)
  edb=# explain analyze select val from t where id = 1::numeric;
                                                QUERY PLAN
  ------------------------------------------------------------------------------------------------------
   Seq Scan on t  (cost=0.00..44608.00 rows=10000 width=17) (actual time=0.013..558.057 rows=1 loops=1)
     Filter: ((id)::numeric = '1'::numeric)
     Rows Removed by Filter: 1999999
   Planning Time: 0.132 ms
   Execution Time: 558.685 ms
  (5 rows)
  edb=#
  

   
  import java.sql.*;
  public class JdbcDataTypeMismatch
  {
     public static void main(String[] args)
     {
       try
       {
         String url      = "jdbc:postgresql://127.0.0.1:4444/edb";
         String user     = "app";
         String password = "abc123";
         Long max        = new Long(10000);
         Class.forName("org.postgresql.Driver");
         Connection c  = DriverManager.getConnection(url, user, password);
         c.setAutoCommit(false);
         PreparedStatement ps = c.prepareStatement("select val from t where id = ?");
         for(Long i = new Long(0); i < max; i++) {
           ps.setLong(1, i);
           ResultSet rs = ps.executeQuery();
           while (rs.next()) {
           }
           rs.close();
           c.commit();
           if(i % 1000 == 0) {
                  System.out.println(i + "/" + max);
           }
         }
       }
       catch(ClassNotFoundException e)
       {
         System.out.println("Class Not Found : " + e.getMessage());
       }
       catch(SQLException e)
       {
         System.out.println("SQL Exception: " + e.getMessage());
       }
     }
  }
  

2018年に中途入社。Oracle Database、EDB Postgres／PostgreSQLの支援経験を積み、2024年からEDB/PostgreSQLサポートを担当。趣味は競馬・麻雀。...show more