Thread

1. Interesting results using new prepared statements

   Barry Lind <barry@xythos.com> — 2002-09-06T23:30:15Z

   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
   
   
   
   
   
   

2. Re: Interesting results using new prepared statements

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-09-12T20:12:35Z

   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
   
   

3. Re: Interesting results using new prepared statements

   Bruce Momjian <pgman@candle.pha.pa.us> — 2002-09-18T04:09:09Z

   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, Pennsylvania 19073