Can I Benefit from and Index Here?

From: Hunter Hillegas <lists(at)lastonepicked(dot)com>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Can I Benefit from and Index Here?
Date: 2003-04-22 16:31:45
Message-ID: BACABC81.A58F2%lists@lastonepicked.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeremiah Jahn 2003-04-22 16:34:46 Re: > 16TB worth of data question
Previous Message Oliver Elphick 2003-04-22 16:27:40 Re: Documentation