Re: Left Join Not Using Index?

On Tue, 22 Apr 2003, Hunter Hillegas wrote:

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

It's deciding that seq scan + sort of all the rows is faster than the
index scan over all the rows (which may very well be true). What does
explain analyze say with enable_seqscan set to true and false?

If you only had extra conditions on one of the two tables and had
appropriate indexes (and reworked the query a little) I'd think you might
be able to get a better plan. As it is, I'm not sure what'd work best
I'd think that a query doing the left join with only the
message_board_topics conditions unioned with an inner join and the
message_board_comments condition would give the same results with some
massaging, but I don't really know if that'd ever perform better anyway.