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.a...@wien.gv.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 >
