On Mon, September 4, 2006 03:56, Gregory Stark wrote: > Thanks, that cleared things up enormously. I'm trying to figure how it > would > react to some of the queries I've written in the past. In particular I'm > thinking of queries like > > WHERE (? OR category = ?) > AND (? OR cost < ?) > AND (? OR description like ?||'%') > > Where I then pass flags in from the application to indicate which search > constraints to apply. If it notices that most searches are for a > particular > set of constraints it would be able to cache plans with the unused > constraints > removed.
Right. That's pretty much the problem as Peter originally described it, I think. > It would not however be able to notice that the last parameter never > contains a % and therefore can use an index scan. If I understand you correctly, then no. If the algorithm sees highly variable values for that last parameter, it will never decide to assume that that parameter will never contain '%'--and I'm not sure how that could be done safely. I do see two glimmers of hope, however: 1. If that last parameter is usually some specific value, then you'll probably end up using specialized plans with that specific value in the parameter's place. If that value is a string without wildcards, you can use your index on description (assuming you have one). If it's '%' or null, the optimizer can decide to ignore the "like" clause. It's only when the scheme finds that it cannot predict what the parameter's value is going to be that you get the generic, poorly-performing code. 2. Once we have a predictor, and assuming it works, it could be tied in with the planner a bit more. As I believe Tom said, the planner can't afford to chase down lots of scenarios just in case they ever happen. But when a parameter is used only for simple matches or inserts on non-indexed columns, for example, the planner might find in the course of its normal activities that there's nothing useful it can do with that parameter and deliver this information with its plan, so that the predictor can ignore the parameter. > I'm also wondering how this interacts with plan stability. Obviously the > direct effect is to throw out any chance of it. But in the long run they > may be two complementary sides of the same thing. Well, it'll cause some plans to be re-generated, surely. But the impression I've gotten from the discussion so far is some that plans were getting too old anyway. Jeroen ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend