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).

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to