avoid prepared statements on complex queries?

From: Anish Kejariwal <anishkej(at)yahoo(dot)com>
To: "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Subject: avoid prepared statements on complex queries?
Date: 2011-11-16 01:18:01
Message-ID: 1321406281.74175.YahooMailNeo@web33903.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

I'm running into an issue where a complex query is performing significantly slower if I use a prepared statement.

I'm using:
-postgres 9.0.3

-postgresql-9.0-801.jdbc4.jar  JDBC driver

Some notes about the query:
-I'm calling a postgres function, via: select * from foo(?,?,?,?,?,?,?)
-I'm getting back 4 million results
-the underlying query is quite complex joining several tables, and the core table is partitioned over a couple hundred tables.

Here's the java code:

conn.setAutoCommit(false);
pstmt = conn.prepareStatement("select * from foo(?,?,?,?,?,?,?)");
pstmt.setFetchSize(1000);

//and, then I do the usual thing to set parameters in the prepared statement
stmt.setInt(1, SOME_NUMBER);
pstmt.setArray(2, genepoolConn.createArrayOf("integer", ARRAY_OF_INTEGERS));
//and so on

What I have found:
-if ARRAY_OF_INTEGERS has a length of 3, the query performs fast
-if ARRAY_OF_INTEGERS has a length of 150, then query takes 1200 seconds
-if ARRAY_OF_INTEGERS has a length of 150, and I don't use a prepared statement, the query takes the expected 30 seconds

So, obviously when using a prepared statement, postgres is coming up with the wrong execution plan when the the parameter list is unexpectedly large.  Pretty understandable.

My question: is there a work around to this?  Can I force it not set the execution plan until I bind the variables?

The only reasons I'm using a prepared statement:
-parameterized queries are far easier to work with than building my query via string concatenation
-minimize chances of SQL Injection

If only there was a way to have parameterized queries without using prepared statements....

Thanks!

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Maciek Sakrejda 2011-11-16 01:31:41 Re: avoid prepared statements on complex queries?
Previous Message Teun Hoogendoorn 2011-11-15 08:59:20 BUG #6293: JDBC driver performance