Re: Can I Benefit from and Index Here?

Tom-

I'd like try to implement something like what you've suggested but I'm not
totally up to speed on what your pseudo-sql would translate to... I'm the
DBA here by default, not by training. ;-)

Hunter

> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Date: Wed, 23 Apr 2003 10:28:10 -0400
> To: Richard Huxton <dev(at)archonet(dot)com>
> Cc: Hunter Hillegas <lists(at)lastonepicked(dot)com>, PostgreSQL
> <pgsql-general(at)postgresql(dot)org>
> Subject: Re: [GENERAL] 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