Re: Speeding up LIKE with placeholders?

Dan Sugalski <dan(at)sidhe(dot)org> writes:
> Would I regret it if I asked where in the source this lies so I could
> go fix it?

If it were easy to fix it would have been fixed before now ...

I have toyed with the notion of converting "var LIKE pattern" to
"var LIKE pattern AND var >= lowbound(pattern) AND var < highbound(pattern)"
where lowbound() and highbound() are actual functions that we leave in
the generated plan, rather than insisting that the planner derive these
bounds before making the plan at all. Then the pattern wouldn't have
to be a true constant. However, it falls down on this problem: what
shall those functions do if the supplied pattern isn't left-anchored at
all? highbound in particular doesn't have a valid result it can give
that's guaranteed larger than all possible values of var. Not to
mention that a full-table index scan is the very last thing you want ---
I think the planner would really be abdicating its responsibilities to
generate a plan with that kind of downside risk.

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.

regards, tom lane