Re: bad planner pick... but why?

<me(at)alternize(dot)com> writes:
> SELECT mov_id FROM oldtables.movies LEFT JOIN oldtables.content ON
> movies.mov_id = content.c_m_id
> WHERE mov_id IN (SELECT DISTINCT rel_movieid FROM infos.rel_persons WHERE
> rel_personid = 40544)

Try dropping the DISTINCT, which is redundant given the IN.

> query #1 is factor 1000 slower, because the two tables "movies" (~40k
> entries) and "content" (~30k entries) seem to be joined prior to filtering
> by the IN (....). any ideas why the planer decides not to first evaluate the
> IN (...) statement in the first case?

8.1 doesn't know anything about rearranging join order in the face of
outer joins. In the second case, the strict WHERE condition applied to
the content table allows it to recognize that the outer join can be
reduced to an inner join, and then it can rearrange the join order.
(If you thought these queries were equivalent, you're wrong.)

FWIW, 8.2 will do better.

regards, tom lane