Re: JDBC prepared statement is not treated as prepared statement - Mailing list pgsql-general

高健 wrote:
> I change my Java program by adding the following:
> 
> org.postgresql.PGStatement pgt = (org.postgresql.PGStatement)pst;
> pgt.setPrepareThreshold(1);
> 
> I can see an entry is in pg_prepared_statements now.

Good.

> But the hyperlink's documentation made me a little confused. I also wonder why the threshold option is
> designed .
>
> The document said:
> 
> ---------------------------------------------
> 
> The PostgreSQL™ server allows clients to compile sql statements that are expected to be reused to
> avoid the overhead of parsing and planning the statement for every execution. This functionality is
> available at the SQL level via PREPARE and EXECUTE beginning with server version 7.3
> 
>  …
> 
>  An internal counter keeps track of how many times the statement has been executed and when it reaches
> the threshold it will start to use server side prepared statements.
> 
>  …
> 
> -----------------------------------------------
> 
> What does < clients to compile sql statements > mean?
> 
> I think that maybe the document  just want to say:
> 
> ------------------------------------------------------------------------------------------------------
> ---------------------------------------
> 
> Before  PG import support for prepared statement,
> 
> PG server must parse and plan statement every time when the client send a request.
> 
> Even when the same statement will be executed many times.
> 
> 
> 
> After PG import support for prepared statement,
> 
> When using those statement which is expected reused, by using prepared statement mechanism,
> 
> PG server can avoid overhead of parsing and planning again and again.
> 
> 
> 
> But in order to use prepared statement, The client also must do something:
> 
> When using psql,
> 
> we need to use Prepare command
> 
> 
> 
> When using java,
> 
> we use java.sql.preparedstatement,
> 
> but it is not engouth: we also need to use  org.postgresql.PGStatement 's  setthreshold method to let
> PG server know.
> 
> 
> 
> The client must do something to let  PG server realize that  client want PG server to use prepared
> statement.
> 
> That is why the docmument say "clients to compile sql statements".

I think that it is helpful to explain what the JDBC driver does internally.

If you do not set the threshold or the threshold is not yet exceeded,
the driver will execute the statement as a simple statement (which
corresponds to libpq's PQexec).

Once the threshold is exceeded, the next execution will prepare
the statement (corresponding to libpq's PQprepare) and then execute
it (like libpq's PQexecPrepared).

Subsequent executions will only execute the named prepared statement.

I think that "compile" in the text you quote stands for "prepare".

> And for the threshold,
> 
> If the threshold has not reached, PG server will consider the sql statement a common one, and will
> parse and plan for it every time.
> 
> Only when the threshold is reached, PG server will realize that client need it to hold the statement
> as prepared ,then parsed it and hold the plan.
> 
> -----------------------------------------------------------------------------------------------------
> 
> 
> 
> Is my understanding right?

Server prepared statements are kept in the private memory of the
PostgreSQL backend process.  If you need a statement only once or
twice, it would be wasteful to keep it around.
The idea is that it is worth the effort only if the statement is executed
more than a couple of times.

Yours,
Laurenz Albe