Thread: Can I Benefit from and Index Here?

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;

QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------
 Unique  (cost=24737.05..24980.65 rows=974 width=380)
   ->  Sort  (cost=24737.05..24761.41 rows=9744 width=380)
         Sort Key: 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((message_board_topics.topic_date)::timestamp with time zone,
'MM.DD.YYYY'::text)
         ->  Merge Join  (cost=17260.42..23018.21 rows=9744 width=380)
               Merge Cond: ("outer".rec_num = "inner".topic_id)
               Filter: ((upper(("outer".topic_name)::text) ~~
'MADBROWSER'::text) OR (upper("outer".topic_body) ~~ 'MADBROWSER'::text) OR
(upper(("outer".topic_author)::text) ~~ 'MADBROWSER'::text) OR
(upper(("inner".comment_author)::text) ~~ 'MADBROWSER'::text))
               ->  Index Scan using message_board_topics_pkey on
message_board_topics  (cost=0.00..1202.44 rows=9744 width=364)
               ->  Sort  (cost=17260.42..17562.60 rows=120870 width=16)
                     Sort Key: message_board_comments.topic_id
                     ->  Seq Scan on message_board_comments
(cost=0.00..5668.70 rows=120870 width=16)

Judging from this output, do you guys think I could benefit from any
indexing or planner tweaking?

Hunter

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

> From: Tom Lane <tgl@sss.pgh.pa.us>
> Date: Wed, 23 Apr 2003 10:28:10 -0400
> To: Richard Huxton <dev@archonet.com>
> Cc: Hunter Hillegas <lists@lastonepicked.com>, PostgreSQL
> <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] Can I Benefit from and Index Here?
>
> 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