Left Join Not Using Index?

From: Hunter Hillegas <lists(at)lastonepicked(dot)com>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Left Join Not Using Index?
Date: 2003-04-23 01:44:20
Message-ID: BACB3E04.A5C2E%lists@lastonepicked.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

I have two tables that model a message board:

Table "public.message_board_topics"
Column | Type | Modifiers
---------------------+-----------------------------+-----------
rec_num | integer | not null
topic_name | character varying(255) |
topic_body | text |
topic_author | character varying(20) |
topic_author_email | character varying(50) |
topic_date | date |
topic_updated | timestamp without time zone |
administrator_topic | boolean |
number_of_comments | integer |
Indexes: message_board_topics_pkey primary key btree (rec_num)
Triggers: RI_ConstraintTrigger_819942,
RI_ConstraintTrigger_819943

Table "public.message_board_comments"
Column | Type | Modifiers
----------------------+------------------------+-----------
rec_num | integer | not null
topic_id | integer |
comment_parent | integer |
comment_name | character varying(255) |
comment_body | text |
comment_author | character varying(20) |
comment_author_email | character varying(50) |
comment_date | date |
Indexes: message_board_comments_pkey primary key btree (rec_num),
message_board_comments_topic_id btree (topic_id)
Triggers: RI_ConstraintTrigger_819941

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

Explain outputs:

Unique (cost=34847.38..35145.38 rows=1192 width=293)
-> Sort (cost=34847.38..34877.18 rows=11920 width=293)
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=26858.21..33007.14 rows=11920 width=293)
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))
-> Sort (cost=2446.01..2475.81 rows=11920 width=265)
Sort Key: message_board_topics.rec_num
-> Seq Scan on message_board_topics
(cost=0.00..712.20 rows=11920 width=265)
-> Sort (cost=24412.20..24818.15 rows=162382 width=28)
Sort Key: message_board_comments.topic_id
-> Seq Scan on message_board_comments
(cost=0.00..7203.82 rows=162382 width=28)

It doesn't seem to be using the index in topic_id... What can I do to help
the planner figure out about that index?

Thanks,
Hunter

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Francisco Reyes 2003-04-23 01:50:47 Schemas permissions vs \dt
Previous Message Crercio O. Silva 2003-04-23 00:04:10 New DBTools Manager 2.1.0