Re: prepared statements suboptimal?

rihad wrote:
> Hi, I'm planning to use prepared statements of indefinite
> lifetime in a daemon that will execute same statements
> rather frequently in reply to client requests.
>
> This link:
> http://www.postgresql.org/docs/8.3/static/sql-prepare.html
> has a note on performance:
>
> In some situations, the query plan produced for a prepared statement
> will be inferior to the query plan that would have been chosen if the
> statement had been submitted and executed normally. [...]
>
> I don't understand why postgres couldn't plan this:
> SELECT foo.e, foo.f
> FROM foo
> WHERE pk=$1 AND b=$2 AND status='1' AND c <= $3;
>
> to be later executed any slower than
>
> SELECT foo.e, foo.f
> FROM foo
> WHERE pk='abcabc' AND b='13' AND status='1' AND c <=
> '2007-11-20 13:14:15';

For example, if the table contains almost no rows in the
beginning, the planner will choose to use a full table schan
even if - say - 'pk' is the primary key.

If you use the same execution plan later when the table is big,
the full table scan will hurt considerably, and you would
be much better of with an index lookup.

Other scenarios are certainly conceivable, but this one is
easy to understand.

> Can I help it make more educated guesses? In what scenarios could
> prepared statements turn around and bite me, being slower than simple
> queries? Is this a real problem in practice? Should I
> "refresh" prepared statements from time to time? If so, how? Only by
> deallocating them and preparing anew? Any knob to tweak for that?

You'll probably have to deallocate them and allocate them anew.

Yours,
Laurenz Albe