Re: Left Join Not Using Index?

On Tuesday, April 22, 2003, at 08:44  PM, Hunter Hillegas wrote:

> I have a left join that doesn't seem to be using an index I created,
> and the
> query's performance needs to improve.

[snip]

> The query is:
>
> SELECT DISTINCT message_board_topics.rec_num,
> message_board_topics.topic_name, message_board_topics.topic_body,
> message_board_topics.topic_author,
> message_board_topics.topic_author_email,
> message_board_topics.topic_updated,
> message_board_topics.administrator_topic,
> message_board_topics.number_of_comments, to_char(topic_date,
> 'MM.DD.YYYY')
> as formatted_date 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
>

I'm not very good at reading EXPLAIN output in Postgres yet, but it
seems like it's all those "upper(table.foo) LIKE upper('madbrowser')"
conditions that are causing the slowness.  For starters, change it to
"upper(table.foo) LIKE 'MADBROWSER'".  Then since you're not using
wildcards there, change it to "upper(table.foo) = 'MADBROWSER'".

  -Ken