Re: Speeding up LIKE with placeholders?

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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