Re: JDBC prepared statement is not treated as prepared statement

Hello :

>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.

Thank you . I think it is an exciting point for PG.

This make it "clever" to choice those always executed sql.

Thanks!

2013/6/18 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>

> 高健 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
>