Tom Lane <tgl@sss.pgh.pa.us> writes:
> I think the planner would really be abdicating its responsibilities to
> generate a plan with that kind of downside risk.
Sure, but what about the risk of using a sequential scan the other 99% of the
time? The downside risk of the index scan is a 5x slowdown or so. The downside
risk of the sequential scan is unbounded.
> You could possibly sidestep this argument by envisioning a query like
> var LIKE ('^' || $1)
> but I doubt that anyone actually writes such things. In the end, LIKE
> is the sort of thing that you really have to run a planning cycle for
> in order to get a reasonable plan.
Actually ^ doesn't mean anything to LIKE. There's no way to anchor a LIKE
pattern except by ensuring it doesn't start with % or _.
I don't know. I wrote code that did "LIKE ?||'%'" on Oracle tons of times and
it always used an index scan. I was really impressed when I first checked
whether that worked and really happy when it did. And it always ran just fine.
In retrospect I would have done something like "LIKE escape(?)||'%'". Except
there's no such function. And if I had to write it myself I would do it in the
application. String manipulation in SQL always being such a pain. And in any
case I would have to check for an empty argument and handle that with some
friendly UI message, which can't be done with a simple function in the query.
So the database would be none the wiser and I still would have been
disappointed if it didn't use the index scan.
In the end it's always possible to fool the planner into producing a bad plan.
It's just got to pick the plan that's most likely to be the one the user
intended and least dangerous. It's hard to picture someone intentionally doing
?||'%' without thinking it would use an index scan. If they didn't check for
leading %s and _s or empty parameters then it was their oversight or they were
expecting it to be slow.
--
greg