Interesting results using new prepared statements

From: Barry Lind <barry(at)xythos(dot)com>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Interesting results using new prepared statements
Date: 2002-09-06 23:30:15
Message-ID: 3D793A87.3080008@xythos.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Barry Lind 2002-09-06 23:30:27 problem with new autocommit config parameter and jdbc
Previous Message Steve Howe 2002-09-06 22:38:54 Re: Rule updates and PQcmdstatus() issue