Re: Left Join Not Using Index?

> -----Original Message-----
> From: Hunter Hillegas [mailto:lists(at)lastonepicked(dot)com]
> Sent: Tuesday, April 22, 2003 9:55 PM
> To: Stephan Szabo
> Cc: PostgreSQL
> Subject: Re: [GENERAL] 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?

Suggestion:
Perform the actual query with seqscan enabled/disabled and see which one
is literally faster.

This guess:
> 40855.59 msec

Certainly seems slower than this one:
> 13108.33 msec (13 rows)

Indicating that the strategy originally chosen should be correct.

How accurate is the estimate on your machine?