Re: Can I Benefit from and Index Here?

Richard Huxton <dev@archonet.com> writes:
> On Tuesday 22 Apr 2003 5:31 pm, Hunter Hillegas wrote:
>> FROM message_board_topics left join
>> message_board_comments on
>> (message_board_comments.topic_id=message_board_topics.rec_num)
>> WHERE
>> upper(topic_name) LIKE upper('madbrowser') OR upper(topic_body) LIKE
>> upper('madbrowser') OR upper(topic_author) LIKE upper('madbrowser') OR
>> upper(message_board_comments.comment_author) LIKE upper('madbrowser') ORDER
>> BY message_board_topics.rec_num DESC;

> Well, you might like to try a functional index on upper(topic_name) etc.

But given the OR structure --- in particular, the fact that he's OR-ing
clauses involving fields of both join relations --- an indexscan isn't
applicable.  For example, there's no point going through the rows of
message_board_topics looking for matches for "upper(topic_name) LIKE
upper('madbrowser')", because every other row in message_board_topics
is also a potential match for any message_board_comments entry that
satisfies the WHERE condition on comment_author.  So none of the WHERE
conditions are actually useful until after the join is formed.

It might work to break the thing down into a union of left-side and
right-side conditions.  For instance

    SELECT .. FROM a left join b on (a.id = b.id)
    WHERE ORed-conditions-on-fields-of-a
    UNION
    SELECT .. FROM a join b on (a.id = b.id)
    WHERE ORed-conditions-on-fields-of-b

This is not necessarily faster (if there are *lots* of matches, the time
needed to do duplicate elimination in the UNION step will hurt).  But it
seems worth a try if the conditions are all individually indexable.

            regards, tom lane