parametrized statements, but always replan?

From: Kevin Murphy <murphy(at)genome(dot)chop(dot)edu>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: parametrized statements, but always replan?
Date: 2006-12-20 19:44:22
Message-ID: 45899296.9040008@genome.chop.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Sorry for the newbie question here. The documentation
(documentation/81/server-prepare.html) isn't clear about this point.
I'm using PG 8.1 with the matching jdbc driver.

Will the following code execute the query with a custom plan based on
the value of var (assuming no use of the prepare threshold)?

prestmt = conn.prepareStatement(query);
prestmt.setString(1, var);
resultSet = prestmt.executeQuery();
// ... use result set ...
resultSet.close();
prestmt.close();

I'd like the safety of parametrized SQL statements combined with the
greater performance (for certain queries) of having every statement
planned based on the specific parameters. Ideally, I'd like to use
prepareStatement() and executeQuery() with out-of-band parameters, but
have the finished statement be custom-planned. Most of my queries
wouldn't benefit from the "plan once, execute many times" model.

I've read documentation/81/server-prepare.html, but somehow it wasn't
clear. It makes it sound as if by default, server-side prepared
statements are not really used ("There are a number of ways to enable
server side prepared statements ..."), which would be good for me.

But looking at my postgresql log, that doesn't seem to be the case; I
see PREPARE, BIND, and EXECUTE lines for each query.

Thanks,
Kevin Murphy

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Dave Cramer 2006-12-20 20:07:22 Re: parametrized statements, but always replan?
Previous Message John LH 2006-12-20 18:22:22 Driver Bug