I am from pgsql-jdbc, so I may not be "in the thread", so please ignore
places where my misunderstanding goes out.
The main two questions, IMHO, is:
1) What is the key to plan cache. Current option is some statement key
(id). Another option would be statement text (you still need to store it
if you want to replan at some point). In this case you can use same plan
for multiple statements going from different sessions. That's the point
Simon was talking about. This should significantly reduce planning,
especially on multiple similar clients. Now, as I understand, every
connection prepare same statements and plan then independent. Such
change would make Application servers prepare new connections much
faster (given they prepare a number of same statements for each
connection, which is the case for my engine). This should work for both
named and unnamed. Note that adding unnamed statements to cache (and not
removing on statement disposal) may require much larger cache.
BTW: This is used by IBM DB2 UDB.
2) Specific plans when parameters are known. This is the point about
using partial index(and sometimes even using full index- i.e.
specifying frequent value of some index or one of two tables in a join).
I'd say the best would be to have generic plan and try to replan,
starting from generic plan results (dispose any possibility that gives
values worse then generic plan). Such a replan should be much faster
then original planning because you have rather high starting point.
Another option is to catch possibilities at original planning and select
correct plan when parameters are known - you check all possible uses
with "this will be frequent value, this will match this partial index,
..." the question is the number of such plans. But since all of them
must be better then generic (and it is possible to make a three, i.e. "A
and B are not frequent" -> "A is frequent" -> "A is frequent and B meets
partial index" and children must be better then parent), I'd say there
won't be many (and you can always limit it's number and leave only the
best if one goes out of number or even collect usages and leave the
plans that are used).
