Thread: Interesting results using new prepared statements

In testing the new 7.3 prepared statement functionality I have come
across some findings that I cannot explain.  I was testing using PREPARE
for a fairly complex sql statement that gets used frequently in my
applicaition.  I used the timing information from:
show_parser_stats = true
show_planner_stats = true
show_executor_stats = true

The timing information showed that 60% of time was in the parse and
planning, and 40% was in the execute for the original statement.  This
indicated that this statement was a good candidate for using the new
PREPARE functionality.

Now for the strange part.  When looking at the execute timings as shown
by 'show_executor_stats' under three different senerios I see:
regular execute  = 787ms    (regular sql execution, not using prepare at
all)
prepare execute  = 737ms    (execution of a prepared statement via
EXECUTE with no bind variable, all values are hardcoded into the
prepared sql statement)
prepare/bind execute  = 693ms    (same as above, but using bind variables)

These results where consistent across multiple runs.  I don't understand
why the timings for prepared statements would be less than for a regular
statement, and especially why using bind variables would be better than
without.  I am concerned that prepared statements may be choosing a
different execution plan than non-prepared statements.  But I am not
sure how to find out what the execution plan is for a prepared
statement, since EXPLAIN doesn't work for a prepared statement (i.e.
EXPLAIN EXECUTE <preparedStatementName>, doesn't work).

I like the fact that the timings are better in this particular case
(upto 12% better), but since I don't understand why that is, I am
concerned that under different circumstances they may be worse.  Can
anyone shed some light on this?

thanks,
--Barry

Barry Lind <barry@xythos.com> writes:
> ... I don't understand
> why the timings for prepared statements would be less than for a regular
> statement, and especially why using bind variables would be better than
> without.  I am concerned that prepared statements may be choosing a
> different execution plan than non-prepared statements.

That's entirely likely if you are using bind variables in the prepared
statements, since the planner will not have access to the same constant
values that it does in a plain SQL statement --- for example, "WHERE foo
= $1" looks a lot different from "WHERE foo = 42" to the planner.

In most cases I'd expect the planner to generate worse plans when given
less info :-( ... but in your particular case it seems to be guessing
slightly wrong.

> But I am not
> sure how to find out what the execution plan is for a prepared
> statement, since EXPLAIN doesn't work for a prepared statement (i.e.
> EXPLAIN EXECUTE <preparedStatementName>, doesn't work).

Hmmm --- I can see the usefulness of that, but it looks like a new
feature and hence verboten during beta.  Maybe a TODO for 7.4?
        regards, tom lane

Tom Lane wrote:
> > But I am not
> > sure how to find out what the execution plan is for a prepared
> > statement, since EXPLAIN doesn't work for a prepared statement (i.e.
> > EXPLAIN EXECUTE <preparedStatementName>, doesn't work).
> 
> Hmmm --- I can see the usefulness of that, but it looks like a new
> feature and hence verboten during beta.  Maybe a TODO for 7.4?

Added to TODO:
o Allow EXPLAIN EXECUTE to see prepared plans

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073