Re: Left Join Not Using Index?

Wow! This query is MUCH faster than my old query...

About 1/8 of the time.

> From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
> Date: Wed, 23 Apr 2003 06:41:27 -0700 (PDT)
> To: Hunter Hillegas <lists(at)lastonepicked(dot)com>
> Cc: PostgreSQL <pgsql-general(at)postgresql(dot)org>
> Subject: Re: [GENERAL] Left Join Not Using Index?
>
>
> On Tue, 22 Apr 2003, Hunter Hillegas wrote:
>
>> Your suggestion didn't really make a whole lot of sense to me... Based on
>> this info, what do you think?
>
> I was wondering if something like (columns removed because I'd go insane
> otherwise, but I think this illustrates it):
>
> select message_board_topics.rec_num from
> message_board_topics where upper(topic_name) LIKE upper('madbrowser')
> union
> select message_board_topics.rec_num from
> message_board_topics where upper(topic_body) LIKE upper('madbrowser')
> union
> select message_board_topics.rec_num from
> message_board_topics where upper(topic_author) LIKE upper('madbrowser')
> union
> select message_board_topics.rec_num from
> message_board_topics,message_board_comments where
> message_board_comments.topic_id=message_board_topics.rec_num
> and upper(message_board_comments.comment_author) LIKE upper('madbrowser')
> order by 1 desc;
>
> with indexes on upper(topic_name), upper(topic_body), etc... was
> both the same and faster.
>
> However, the best solution is probably some sort of full text indexing
> solution. Putting the keywords from the various columns you want to index
> along with the rec_num (or topic_id) of the row and an index on the text.
> Then you could join message_board_topics with that and probably get a much
> better plan.