Thread: parametrized statements, but always replan?

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

Kevin,

The only time prepared statements won't replan is if you re-use the
prepared statement, which your code does not.

However... AFAIK, postgresql does not plan based on the parameter.

Dave
On 20-Dec-06, at 2:44 PM, Kevin Murphy wrote:

> 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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>

Set the prepareThreshold to 0, meaning that it will never create a
"real" server-side prepared statement.

There will still be a protocol level PREPARE/BIND/EXECUTE passed to the
back-end because that's the way the V3 protocol works, but that's not
the same thing.  The planner will still see the actual query values.

-- Mark Lewis

On Wed, 2006-12-20 at 14:44 -0500, Kevin Murphy wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

Dave Cramer wrote:
> The only time prepared statements won't replan is if you re-use the
> prepared statement, which your code does not.
>
> However... AFAIK, postgresql does not plan based on the parameter.

OK, PG definitely plans based on the parameter value.   I turned on plan
logging in the server log, and without specifying prepareThreshold at
all (e.g. setting it to 0), I can see that PG is making different plan
estimates for different parameter values.  It might be using the same
general plan, but I can't bear to wade through all that output to tell.
Looking at ANALYZE output on the unadorned, non-prepared query, I can
see that different plans may be made, depending on the parameter.  In a
table with millions of rows, there are tens of thousands of distinct
values of this parameter, with a very wide distribution.  One value has
200K occurrences, many others just a handful.  Using a different plan
for the outliers can be (and is) very helpful.  When I wrap the query in
a SQL PREPARE statement, a generic plan is made that works OK for 99.9%
of the column values, but for the most common column value, that plan is
20,000 times slower than the plan that is chosen by the planner when it
knows that the query involves that most common value.  Hence my interest
in what is going on behind prepareStatement().  I'm good now.

-Kevin Murphy

Mark Lewis wrote:
> Set the prepareThreshold to 0, meaning that it will never create a
> "real" server-side prepared statement.
Hmm, even by default (without specifying prepareThreshold), it seems
that the planner looks at the parameter values for the first execution
of the query.  (See my post a couple minutes ago*).  Am I mistaken?

If I'm not mistaken, then for one-shot queries, is there any any
additional advantage to setting prepareThreshold to 0?

BTW, is the default value of prepareThreshold is 1?

Thanks,
Kevin Murphy

* From my post a couple of minutes ago:
> OK, PG definitely plans based on the parameter value.   I turned on
> plan logging in the server log, and without specifying
> prepareThreshold at all (e.g. setting it to 0), I can see that PG is
> making different plan [cost] estimates for different parameter values.

On Wed, 2006-12-20 at 16:28 -0500, Kevin Murphy wrote:
> Mark Lewis wrote:
> > Set the prepareThreshold to 0, meaning that it will never create a
> > "real" server-side prepared statement.
> Hmm, even by default (without specifying prepareThreshold), it seems
> that the planner looks at the parameter values for the first execution
> of the query.  (See my post a couple minutes ago*).  Am I mistaken?
>
> If I'm not mistaken, then for one-shot queries, is there any any
> additional advantage to setting prepareThreshold to 0?

With the current driver version, there is no advantage if you only use
the PreparedStatement once.

> BTW, is the default value of prepareThreshold is 1?

The default value in the driver version I've got handy (forget exactly
which version this is) is 5.  I don't think that's changed recently.

-- Mark Lewis

Dave Cramer <pg@fastcrypt.com> writes:
> The only time prepared statements won't replan is if you re-use the
> prepared statement, which your code does not.
> However... AFAIK, postgresql does not plan based on the parameter.

This was changed in 8.2: for an unnamed statement the planning step is
delayed until Bind time, so that the actual parameter values can be
considered.

            regards, tom lane