"Tom Lane" <[EMAIL PROTECTED]> wrote > > Yeah. The LIKE index optimization depends on seeing a constant LIKE > pattern at plan time --- otherwise the planner doesn't know what > indexscan parameters to generate. So a bound-parameter query loses. >
AFAICS the problem is not restricted to LIKE, we can easily find a lot of similar problems caused by the actual parameters. For example, SeqScan vs. IndexScan vs. BitmapIndexScan for a range query. So an improvement is definitely needed. > Ideas for improving this situation are welcome ... it's not an easy > problem ... > IMHO basically we have two ways to get better plan: one is to have a set of alternative plans for prepare queries. This will add some cost but PREPARE is supposed to do only once against a lot of EXECUTE. But still, the biggest problem is that number of plans is not controllable. Another way is to generate a plan on the fly. What we do is to let some REPLAN nodes sit on top of some critical plan node: at the execution, we will compare the actual numbers we get and the estimated number we have (mabye "rows"?), once we find that a re-plan efforts might be deserved, we will get a new plan on the fly. In this way, I think a not-too-big patch will do. I remember there is a paper talking about this somewhere but not remember clearly. -- This method can handle the range query problem above, but not for LIKE. So we may have to kludge some code to handle LIKE especially :-(. Regards, Qingqing ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
