Re: bad planner pick... but why?

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

thanks. that subquery is lightning fast already, tho ;-)

> (If you thought these queries were equivalent, you're wrong.)

i know. but as the tables have more or less the same size and are corelated,
having such a huge difference puzzled me

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

so in other words, at the moment i cannot change anything if i need to
filter for mov_id (query 1) and thus do need an outer join?

> FWIW, 8.2 will do better.

looking forward to 8.2, but as a full dump/reload takes around 3hrs
(8.0->8.1) its not something i want to do on a live system for the moment
;-)

regards,
thomas