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

Richard Huxton <dev@archonet.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

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