Re: prepared query performs much worse than regular query

On May 21, 2010, at 8:26 PM, Matthew Wakeling wrote:

> On Fri, 21 May 2010, Richard Yen wrote:
>> Any ideas why the query planner chooses a different query plan when using prepared statements?
>
> This is a FAQ. Preparing a statement makes Postgres create a plan, without
> knowing the values that you will plug in, so it will not be as optimal as
> if the values were available. The whole idea is to avoid the planning cost
> each time the query is executed, but if your data is unusual it can
> result in worse plans.
>

Two things I disagree with.
1. The "whole idea" is not just to avoid planning cost. It is also to easily avoid SQL injection, reduce query parse time, and to make client code cleaner and more re-usable.
2. The data does not need to be "unusual". It just needs to have a skewed distribution. Skewed is not unusual (well, it would be for a primary key :P ).

Maybe the planner could note a prepared query parameter is on a high skew column and build a handful of plans to choose from, or just partially re-plan on the skewed column with each execution.
Or make it easier for a user to have a prepared statement that re-plans the query each time. Even just a per connection parameter "SET prepared.query.cacheplan = FALSE"

> Matthew
>
> --
> Existence is a convenient concept to designate all of the files that an
> executable program can potentially process. -- Fortran77 standard
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance