PostgreSQL uses PreparedStatement to cause slow query analysis

Lab Environment:

DB is PostgreSQL version 8.2.15

JDK1.8

Test A

Query a SQL using JDBC:

 Public Static voidtest1 (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_end Point_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:" ); LongS_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 (); }            }        }    }

Results:

Start Query1:
Using time:11519 ms

Test Two

Query the same SQL using JDBC PreparedStatement:

 Public Static voidtest2 (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_end                Point_location_hours L "+" where l.org_id =? " + "and Date_time >=?" < 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:"); LongS_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 (); }            }        }    }

Results:

Start Query2:
Using time:143031 ms

The same SQL, test two and test the results for what difference is so big?

Test one of the SQL did not use the PreparedStatement method, directly to the original SQL. Test two uses PreparedStatement, but the set parameter is used with a string.

Is it strange that the two queries are 10 times times the same speed?

Now let's do another experiment:

Test Three

Query the same SQL using JDBC PreparedStatement:

 Public Static voidtest3 (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_end                Point_location_hours L "+" where l.org_id =? " + "and Date_time >=?" < 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:"); LongS_time =System.currenttimemillis (); Conn=drivermanager.getconnection (URL, props); Presta=conn.preparestatement (SQL2); intorg_id = 195078; SimpleDateFormat DF=NewSimpleDateFormat ("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,NewJava.sql.Timestamp (D1.gettime ())); Presta.settimestamp (3,NewJava.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 (); }            }        }    }

Results:

Start Query3:
Using time:16245 ms

The test results are similar to those of the test one, why?

This test also uses PreparedStatement, but specifies the type of the parameter when the parameter is set.

Explan Analyze

View 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.lo                   Cation_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.or g_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= $and Date_time>= $ and Date_time < $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);d EV=# EXECUTE Test (195078, ' 2017-04-11 00:00:00.0 ', ' 2017-04-20 00:00:00.0 ', 195078);

time:98794.544 ms

Conclusion

PostgreSQL uses the table type when using the original SQL, and can effectively filter the results based on the where condition.

When a parameter is used with a string, without specifying a type, PostgreSQL does not convert the type first, but instead scans all the data and filters the results based on the where condition for all data.

When a query parameter specifies a type, PostgreSQL can first filter the result based on the where condition.

Related connections:

It seems when using JDBC with prepare statement, the query would be a 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 uses PreparedStatement to cause slow query analysis