Re: Force a merge join?

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Wed, May 15, 2002 at 03:31:30PM -0400, Doug Fields wrote:
> [Much snipped about mergejoins]
>> AND LOWER(a.email) = LOWER(b.email);

> There's your problem. You're not comparing the two columns, you're comparing
> the two columns after running through a function, so it can't use the index.
> Try creating an index on LOWER(email) instead of just email.

I don't think that will help :-( ... (in fact, it kinda looked like he'd
done that already, though surely the version he's using isn't saying so
explicitly).

The current version of the optimizer is not bright enough to do either
merge or hash joins on join expressions more complex than var1 = var2.
Improving this is on the TODO list ...but in the meantime I wonder why
you couldn't force an email-address column to lower case when you store
it, so as to simplify the join problem.  The RFCs nominally allow the
local-part of an email address to be case sensitive, but in practice
there is no one who really expects a case-sensitive email address to
work.

            regards, tom lane