Re: Can I Benefit from and Index Here?

Richard Huxton <dev(at)archonet(dot)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