Re: Left Join Not Using Index?

On Tue, 22 Apr 2003, Hunter Hillegas wrote:

> Thanks for responding...
>
> With enable_seqscan = false:

SNIP

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

Note the actual time on the merge join here (about 8000 msec):

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

Note the sort is showing ~ 4000 to 5000 msec

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

Run time:

> Total runtime: 13108.33 msec
> (13 rows)

So, this query, using the seq scan, is 3 times faster. I.e. the planner
made the right move on the seq scan versus the index scan.

There may be a faster way than using a merge join, but more than likely,
your biggest gain will come from tuning postgresql and your OS to handle
more data at a time therefore faster.

What are your settings for sort_mem, shared_buffers, effective_cache_size?

They're all explained in the 3.4.2. Planner and Optimizer Tuning section
of the 7.3.2 docs quite well.

Look at changing any of these to off and see how it affects the planner as
well.

enable_seqscan
enable_indexscan
enable_tidscan
enable_sort
enable_nestloop
enable_mergejoin
enable_hashjoin

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

Well, the problem with the suggestion was that while it did turn on index
scans, it actually resulted in slower performance, since a seq scan proved
3 times faster. Barring other minor errors in the query planner, you can
either make the machine faster / tune postgresql, change your query to
something that runs faster by the nature of how it's written. Maybe
having a more selective where clause or using one in a subselect that will
be run first, thus knocking down the amount of data your database has to
sling around, or go to full text indexing. There were two or three
solutions in the contrib directory last I looked that both were way faster
than the typical "roll your own" solutions.