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
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 |