Re: Left Join Not Using Index?

Thanks for responding...

With enable_seqscan = false:

Unique (cost=545747.57..546045.57 rows=1192 width=293) (actual
time=40851.49..40854.80 rows=51 loops=1)
-> Sort (cost=545747.57..545777.37 rows=11920 width=293) (actual
time=40851.48..40852.09 rows=292 loops=1)
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=0.00..543907.33 rows=11920 width=293) (actual
time=482.05..40847.19 rows=292 loops=1)
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..2532.12 rows=11920 width=265) (actual
time=428.32..4893.13 rows=11920 loops=1)
-> Index Scan using message_board_comments_topic_id on
message_board_comments (cost=0.00..535662.04 rows=162382 width=28) (actual
time=28.45..32163.18 rows=162382 loops=1)
Total runtime: 40855.59 msec

With enable_seqscan = true:

Unique (cost=34847.38..35145.38 rows=1192 width=293) (actual
time=13005.13..13008.51 rows=51 loops=1)
-> Sort (cost=34847.38..34877.18 rows=11920 width=293) (actual
time=13005.12..13005.73 rows=292 loops=1)
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)
(actual time=4930.32..12949.93 rows=292 loops=1)
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)
(actual time=628.30..953.50 rows=11920 loops=1)
Sort Key: message_board_topics.rec_num
-> Seq Scan on message_board_topics
(cost=0.00..712.20 rows=11920 width=265) (actual time=0.10..223.96
rows=11920 loops=1)
-> Sort (cost=24412.20..24818.15 rows=162382 width=28)
(actual time=4301.14..5788.66 rows=162382 loops=1)
Sort Key: message_board_comments.topic_id
-> Seq Scan on message_board_comments
(cost=0.00..7203.82 rows=162382 width=28) (actual time=0.10..1335.26
rows=162382 loops=1)
Total runtime: 13108.33 msec
(13 rows)

Your suggestion didn't really make a whole lot of sense to me... Based on
this info, what do you think?

Hunter

> From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
> Date: Tue, 22 Apr 2003 21:42:02 -0700 (PDT)
> To: Hunter Hillegas <lists(at)lastonepicked(dot)com>
> Cc: PostgreSQL <pgsql-general(at)postgresql(dot)org>
> Subject: Re: [GENERAL] 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.
>
>