Re: [HACKERS] Interesting index/LIKE/join slowness problems

Ole Gjerde <gjerde@icebox.org> writes:
> On Fri, 16 Jul 1999, Tom Lane wrote:
>> The trouble with OR-of-ANDs is entirely the optimizer's fault; the
>> executor would do them fine if the optimizer would only hand them over
>> in that form.

> Since we really need to have this work, or go with a different database,
> we would be willing to pay someone to fix this problem.  Would anybody be
> interested in doing this, how soon and how much?  It would be preferable
> that this would be a patch that would be accepted back into postgres for
> 6.6.

Fixing the general OR-of-ANDs problem is going to be quite ticklish,
I think, because it would be easy to make other cases worse if we're
not careful about how we rewrite the qual condition.

However, I had an idea yesterday about a narrow, localized fix for LIKE
(and the other ops processed by makeIndexable), which I think would meet
your needs if the particular cases you are concerned about are just ORs
of LIKEs and simple comparisons.

It goes like this: while we want LIKE to generate indexable comparisons
if possible, having the parser insert them into the parsetree is a
really crude hack.  The extra clauses are just a waste of cycles under
many scenarios (no index on the field being looked at, LIKE not in the
WHERE clause or buried too deeply to be an indexqual, etc etc).
What's worse, the parser doesn't know for sure that what it's
manipulating really *is* a LIKE --- it's making an unwarranted
assumption on the basis of the operator name, before the actual operator
has been looked up!  So I've wanted to replace that method of optimizing
LIKE since the moment I saw it ;-)

What would be better would be to teach the indexqual extractor in the
optimizer that it can make indexqual conditions from a LIKE operator.
Then, the LIKE just passes through the cnfify() step without getting
rewritten, so we don't have the OR-of-ANDs problem.  Plus we don't pay
any overhead if the LIKE can't be used as an indexqual condition for any
reason.  And by the time the optimizer is acting, we really know whether
we have a LIKE or not, because type resolution and operator lookup have
been done.

I don't know how soon the general OR-of-ANDs problem can be solved,
but I am planning to try to make this LIKE fix for 6.6.  If you want
to send some $$ my way, all the better...
        regards, tom lane