Re: Left Join Not Using Index?

Those plans are from 'EXPLAIN ANALYZE', not 'EXPLAIN'... So as I understand
it, that's the actual run time for the query.

Having seqscan turned on is a lot faster, but isn't that the default? The
reason I was given this query to look at was that it's not running fast
enough right now in our production application...

So, anyone have any suggestion about anything else I could do? Those indices
don't help at all?

Hunter

> From: "Dann Corbit" <DCorbit(at)connx(dot)com>
> Date: Tue, 22 Apr 2003 22:00:12 -0700
> To: "Hunter Hillegas" <lists(at)lastonepicked(dot)com>, "Stephan Szabo"
> <sszabo(at)megazone23(dot)bigpanda(dot)com>
> Cc: "PostgreSQL" <pgsql-general(at)postgresql(dot)org>
> Subject: RE: [GENERAL] 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?