Re: Can I Benefit from and Index Here?

On Tuesday 22 Apr 2003 5:31 pm, Hunter Hillegas wrote:
> I have a query that is taking longer and longer to run, so I am starting to
> look at optimizing it a bit... The query is as follows:
>
> explain 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;

Well, you might like to try a functional index on upper(topic_name) etc. No
point in a straightforward index. I'm assuming you have indexes on the join
columns (topic_id, rec_num)?

The other thing that leaps out is that you're using LIKE where a simple "="
will do. PG should be able to use an index for this though, since it's
anchored on the left.

--
Richard Huxton