Very strange performance decrease when reusing a PreparedStatement

Hi everybody,

I am experiencing a *very* strange problem when querying Postgres
through JDBC using PreparedStatements.

To say it short, for the same SELECT query :

- when reusing a single PreparedStatement the average response time
per query is 60 milliseconds

- when creating (and closing) a new PreparedStatement each time, the
average response time drops to only 2 milliseconds !

This seems unbelievable, but I cross-checked with several people, and
this is truly what happens. Maybe some cleaning or synchronizing is
done before executing again the same statement ? Has anybody
experienced this ?

This appears on two different 8.x versions of postgresql, on Mac and
Linux. The client runs under Mac/Java 5.

Here are a little bit more details :

At first I used to create a single prepared statement once in my
constructor :

this.stmt = getConnection().prepareStatement("SELECT resource.uri FROM
literal,resource WHERE resource.id=literal.id_subject AND
literal.id_graph=? AND literal.id_predicate=? AND literal.lexical_form
LIKE ?");

And then, for each new query :

this.stmt.setLong(1, graphID);
this.stmt.setLong(2, relationID);
this.stmt.setString(3, litteralValue);
ResultSet rs = this.stmt.executeQuery();
...
rs.close();


This gave me very poor performance (about 60ms/query). Just for
testing I added the following lines, and I got my 2 milliseconds per
query (you can double check that the statement creation is exactly the
same) :

if(this.stmt != null)
    this.stmt.close();
this.stmt = getConnection().prepareStatement("SELECT resource.uri FROM
literal,resource WHERE resource.id=literal.id_subject AND
literal.id_graph=? AND literal.id_predicate=? AND literal.lexical_form
LIKE ?");


Many thanks for your help.

Best regards,
--
Frédérik Bilhaut