Prepared statement performance... - Mailing list pgsql-general

Hi, everybody.

I am running into huge performance problems, due to JDBC not being able to cache query plans :-(
My java program runs the (set of about 5) identical statements with different parameters for about 30 million times...
What I am talking about below has to do with JDBC, but is not really specific to it - it seems to me, that the backend
itself
could be improved to better handle this kind of scenario, when the same statement is executed many times in the same
session.

It takes about 3 weeks(!) to run, and it looks like about *half* of that time is spent by the query planner,
creating and recreating the query plans every time I ran the damn statement....

I am looking into implementing some kind of a solution, that would let me work around that problem...
So far, I only see two possibilities:

- a general solution, that would involve extending postgres SQL gramma to include a 'prepare' statement
- or am ugly work around, that would involve moving all my sql statements into stored procedures, and have
   those cache the query plans inside...

The second solution is not only ugly (because it requires the application code to be changed and to have a specialized
stored procedure for every query), but also requires some additional hacks (to overcome the hard limit on the number of
function arguments and the inability for functions to return tuples) - the only way I imagine this can be made to work
is
to glue all the arguments together into a text string, and have the stored procedure parse it back, execute the query,
then
glue the resulting tuple(s) into another text string, return it, and have the application (or, perhaps, JDBC layer)
parseit back 
into columns...

I was wonderring if anybody has any better ideas how this can be made to work (I am looking for a solution that would
minimize changes to the existing JDBC applications that use PreparedStatements)?

If the maintainers of the involved code are interested, I would be willing to implement and contribute the solution we
comeup with ... 
(I figure, nobody would really be interested in getting that second solution I mentioned into the mainstream :-), but,
ifwe are 
able to come up with something more general and less ugly, perhaps, I am not the only one who would be able to
contributefrom 
it)...

For example, I believe, it should not be too complicated to implement that first possibility I described above...
The way I see it would involve adding two statements to postgres SQL syntax:

prepare <name> as <sql statement>
and
execute <name> with (<parameter list>)

For example:

prepare mystatement as select * from mytable where id = $1 and name like $2;

and then

execute mystatement with (1, 'Dima');
execute mystatement with (2, 'John');

etc....

The JDBC driver would then send the 'prepare' command to the backend in Connection.prepareStatement (), and
use the 'execute' in PreparedStatement.execute ();

One potential problem with implementation I see here is that the query planner wants to know the argument types ahead
oftime... 
I guess, I could get around that by making all the arguments 'text', and having them casted into the right types when
the
statement is actually executed.

There is, probably a need to also have some kind of a 'close' command to throw away the prepared query plans... or we
couldjust 
make them last forever until, say, the end of transaction (or, perhaps, the end of the session?)...

If there is anyone interested in discussing various possibilities, and getting this implemented one way or another,
  I would like to hear from you!

Thanks!

Dima.