PostgreSQL 使用 PreparedStatement 導致查詢慢的分析

標籤：timezone   ack   exec   null   tin   overflow   public   text   rom   

實驗環境：

DB is PostgreSQL version 8.2.15 

JDK1.8

測試一

使用JDBC查詢一個SQL：

public static void test1(String url, Properties props){        String sql = "SELECT l.src_ip, l.location_id, "                + "SUM(l.us_bytes) as up_usage, "                + "SUM(l.ds_bytes) as down_usage, "                + "(SUM(l.us_bytes) + SUM(l.ds_bytes) ) as total_usage "                + "FROM unmapped_endpoint_location_hours l "                + "where l.org_id = 195078 "                + "AND date_time >= ‘2017-04-01 00:00:00.0‘ AND date_time < ‘2017-04-08 00:00:00.0‘ "                + "AND l.location_id in (2638,2640,2654 ) "                + "GROUP BY l.src_ip, l.location_id ";                Connection conn = null;        Statement sta = null;        try {            System.out.println("Start query1:" );            long s_time = System.currentTimeMillis();            conn = DriverManager.getConnection(url, props);            sta = conn.createStatement();            sta.execute(sql);            System.out.println("Using Time: " + (System.currentTimeMillis() - s_time));        } catch (SQLException e) {            e.printStackTrace();        } finally {            if (conn != null) {                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }            if (sta != null) {                try {                    sta.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }    }

結果：

Start query1:
Using Time: 11519 ms

測試二

使用JDBC PreparedStatement 查詢相同的SQL：

public static void test2(String url, Properties props){        String sql2 = "SELECT l.src_ip, l.location_id, "                + "SUM(l.us_bytes) as up_usage, "                + "SUM(l.ds_bytes) as down_usage, "                + "(SUM(l.us_bytes) + SUM(l.ds_bytes) ) as total_usage "                + "FROM unmapped_endpoint_location_hours l "                + "where l.org_id = ? "                + "AND date_time >= ? AND date_time < ? "                + "AND l.location_id in (2638,2640,2654 ) "                + "GROUP BY l.src_ip, l.location_id";                Connection conn = null;        PreparedStatement preSta = null;        try {            System.out.println("Start query2:");            long s_time = System.currentTimeMillis();            conn = DriverManager.getConnection(url, props);            preSta = conn.prepareStatement(sql2);            preSta.setString(1, "195078");            preSta.setString(2, "2017-04-01 00:00:00.0");            preSta.setString(3, "2017-04-09 00:00:00.0");            preSta.executeQuery();            System.out.println("Using Time: " + (System.currentTimeMillis() - s_time));        } catch (SQLException e) {            e.printStackTrace();        } finally {            if (conn != null) {                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }            if (preSta != null) {                try {                    preSta.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }    }

結果：

Start query2:
Using Time: 143031 ms

相同的SQL，測試二和測試一結果為什麼差別這麼大？

測試一的SQL沒有使用PreparedStatement 方式，直接給了原始的SQL。測試二的使用了PreparedStatement ，但是在set參數的時候用的都是String。

兩者查詢速度相差10倍，這是不是很奇怪？

現在來做另一個實驗：

測試三

使用JDBC PreparedStatement 查詢相同的SQL：

public static void test3(String url, Properties props){        String sql2 = "SELECT l.src_ip, l.location_id, "                + "SUM(l.us_bytes) as up_usage, "                + "SUM(l.ds_bytes) as down_usage, "                + "(SUM(l.us_bytes) + SUM(l.ds_bytes) ) as total_usage "                + "FROM unmapped_endpoint_location_hours l "                + "where l.org_id = ? "                + "AND date_time >= ? AND date_time < ? "                + "AND l.location_id in (2638,2640,2654 ) "                + "GROUP BY l.src_ip, l.location_id";                Connection conn = null;        PreparedStatement preSta = null;        try {            System.out.println("Start query3:");            long s_time = System.currentTimeMillis();            conn = DriverManager.getConnection(url, props);            preSta = conn.prepareStatement(sql2);                        int org_id = 195078;            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");            TimeZone.setDefault(TimeZone.getTimeZone("UTC"));            Date d1 = null;            Date d2 = null;            try {                d1 = df.parse("2017-04-01 00:00:00");                d2 = df.parse("2017-04-09 00:00:00");            } catch (ParseException e1) {                e1.printStackTrace();            }            preSta.setInt(1, org_id);            preSta.setTimestamp(2, new java.sql.Timestamp(d1.getTime()));            preSta.setTimestamp(3, new java.sql.Timestamp(d2.getTime()));            preSta.executeQuery();            System.out.println("Using Time: " + (System.currentTimeMillis() - s_time));        } catch (SQLException e) {            e.printStackTrace();        } finally {            if (conn != null) {                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }            if (preSta != null) {                try {                    preSta.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }    }

結果：

Start query3:
Using Time: 16245 ms

測試結果和測試一的結果差不多，為什嗎？

這次測試同樣使用了PreparedStatement，但是在設定參數的時候指定了參數的類型。

explan analyze

查看explan

dev=# explain analyze SELECT count(loc.name) AS totalNumdev-# FROM (SELECT t.src_ip, t.location_id, t.up_usage, t.down_usage, t.total_usagedev(#       FROM (SELECT l.src_ip, l.location_id,dev(#                   SUM(l.us_bytes) as up_usage,dev(#                   SUM(l.ds_bytes) as down_usage,dev(#                   (SUM(l.us_bytes) + SUM(l.ds_bytes) ) as total_usagedev(#             FROM unmapped_endpoint_location_hours ldev(#             where l.org_id = 195078dev(#                   AND date_time >= ‘2017-04-11 00:00:00.0‘ AND date_time < ‘2017-04-20 00:00:00.0‘dev(#                   AND l.location_id in (2638,2640)dev(#                   GROUP BY l.src_ip, l.location_id ) tdev(# WHERE t.total_usage > 0.0 ) mdev-# LEFT OUTER JOIN locations loc on m.location_id = loc.id WHERE loc.org_id = 195078;

Time: 15202.518 ms

Prepare Expalin:PREPARE  test(int,text,text,int) asSELECT count(loc.name) AS totalNumFROM (SELECT t.src_ip, t.location_id, t.up_usage, t.down_usage, t.total_usage      FROM (SELECT l.src_ip, l.location_id,                  SUM(l.us_bytes) as up_usage,                  SUM(l.ds_bytes) as down_usage,                  (SUM(l.us_bytes) + SUM(l.ds_bytes) ) as total_usage            FROM unmapped_endpoint_location_hours l            where l.org_id = $1                  AND date_time >= $2 AND date_time < $3                  AND l.location_id in (2638,2640)                  GROUP BY l.src_ip, l.location_id ) tWHERE t.total_usage > 0.0 ) mLEFT OUTER JOIN locations loc on m.location_id = loc.id WHERE loc.org_id = $4;Explain analyze EXECUTE test(195078,‘2017-04-11 00:00:00.0‘,‘2017-04-20 00:00:00.0‘,195078);dev=# EXECUTE test(195078,‘2017-04-11 00:00:00.0‘,‘2017-04-20 00:00:00.0‘,195078);

Time: 98794.544 ms

 

結論

PostgreSQL 在使用原始SQL的時候會用表中類型來查，能有效根據where條件過濾結果。

當參數都是使用String的時候，沒有指定類型時，PostgreSQL沒有先做類型轉換，而是掃描了所有的資料，對所有的資料根據where條件過濾結果。

當查詢參數指定類型的時候，PostgreSQL可以先根據where條件過濾結果。

 

相關串連：

It seems when using JDBC with prepare statement, the query will be slow in postgresql:

http://www.postgresql-archive.org/Slow-statement-when-using-JDBC-td3368379.html

http://grokbase.com/t/postgresql/pgsql-general/116t4ewawk/reusing-cached-prepared-statement-slow-after-5-executions

https://stackoverflow.com/questions/28236827/preparedstatement-very-slow-but-manual-query-quick

 

PostgreSQL 使用 PreparedStatement 導致查詢慢的分析