Re: Cached/global query plans, autopreparation

The heuristic for choosing the generic plan by comparing the estimated costs
of the generic plan to the estimated cost of the specific plans is flawed. 
Consider this:  what is the selectivity of a predicate such as 'x > $1'? 
The planner can only make a wild guess, and the default wild guess for range
selectivity is 1/3.

Suppose that the true predicate selectivity is 2/3.  After executing good
specific plans 5 times, we compare the estimated cost of the generic plan to
the average estimated cost of the specific plans.  We conclude that we
should switch to the generic plan because the (badly) estimated cost is
less.  You may get the same plan.  If it's your lucky day, you might even
get a better plan, but in this situation the generic plan should be worse,
on average.  We consider the accuracy of estimates to be the same in both
cases, which is wrong.

So the decision to use the generic plan or not by comparing the estimated
cost of the generic plan to the average estimated cost of k specific plans
uses flawed logic.  If the planner is not called after the plan is cached,
then it becomes more difficult to tune it (e.g. by experimenting with
different plans using pg_hint_plan).  You'd probably have to deallocate and
re-prepare to get another 5 tries.  That sounds annoying.

Is there a way to EXPLAIN the generic plan? 




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html