Re: bad planner pick... but why?
От | |
---|---|
Тема | Re: bad planner pick... but why? |
Дата | |
Msg-id | 079301c6f209$45f43ea0$6501a8c0@iwing обсуждение исходный текст |
Ответ на | bad planner pick... but why? (<me@alternize.com>) |
Список | pgsql-novice |
> FWIW, 8.2 will do better. i can confirm this is fixed for 8.2b1 - the query runs in 2ms now. thanks for the great work in planner improvement! - thomas ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: <me@alternize.com> Cc: <pgsql-novice@postgresql.org> Sent: Monday, October 16, 2006 6:17 PM Subject: Re: [NOVICE] bad planner pick... but why? > <me@alternize.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 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
В списке pgsql-novice по дате отправления: