CODEWITHSUNDEEP
javapostgresqljdbc
Jijo Mathew
Jijo Mathew

Reputation: 352

Execution of a Postgresql query using JDBC taking much more time compared to execution of query in pgadmin

I have a stored procedure, with an argument(Parameter)

Let us call it x.

When I run, 

select * from x('myParameter')

It completes in 4 minutes.

Now I try to execute the same stored prcedure in java as follows:-

PreparedStatement statement = connection.prepareStatement("Select * from x('myParameter')");
org.postgresql.PGStatement pgstmt = (org.postgresql.PGStatement)statement;
pgstmt.setPrepareThreshold(1);
boolean usingServerPrepare = pgstmt.isUseServerPrepare();
System.out.println(usingServerPrepare);
statement.execute();

This takes approximately 26 hours to complete.

I was initially trying without pgstmt.setPrepareThreshold(1);, i.e. Default value for prepareThreshold. The result was the same.

Then I tried the java program with "set enable_nestloop false" as the first line of the stored procedure and it completes in 4 minutes.

I am not able to understand why the query changes the execution plan, when I am explicitly forcing server side prepared statement.

I am using Postgres 9.4 and Java 8. I am also using PGBouncer for connection pooling.

My query here is - Why is JDBC influencing the execution plan if at all? Is there a way to force server side execution plan to my query triggered from java program?

Upvotes: 2

Views: 1704

Answers (1)

Jijo Mathew
Jijo Mathew

Reputation: 352

Got the solution:- There is a table used within the stored procedure say "tab1".

When we run stored procedure from PGAdmin/SQL window tab1 is already created/modified hours back.

In the automated running, using java, tab1 is created and loaded/modified just seconds before the execution of the stored procedure.
In the first case(Running stored procedure from PGAdmin/SQL window) auto-analyze on tab1 has finished by the time we run the stored procedure in PGAdmin, whereas in the second case(automated running using java) auto-analyze on tab1 is yet to start or is under process as the tab1 has just been modified .
This causes Postgres to choose a better plan at the time of running the query from PGAdmin, as compared to the time when we run using java.

Summary- Different plans were chosen because In the second case, auto-analyze did not complete by the time we ran the stored proc, resulting in a poor query plan selection.

Upvotes: 4

Related Questions