Re: Left Join Not Using Index? - Mailing list pgsql-general

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.