Skip site navigation (1) Skip section navigation (2)

Re: bad planner pick... but why?

From: <me(at)alternize(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: bad planner pick... but why?
Date: 2006-10-17 16:28:25
Message-ID: 079301c6f209$45f43ea0$6501a8c0@iwing (view raw or flat)
Thread:
Lists: 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(at)sss(dot)pgh(dot)pa(dot)us>
To: <me(at)alternize(dot)com>
Cc: <pgsql-novice(at)postgresql(dot)org>
Sent: Monday, October 16, 2006 6:17 PM
Subject: Re: [NOVICE] 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 



In response to

pgsql-novice by date

Next:From: Lane Van IngenDate: 2006-10-17 18:33:16
Subject: How to Substract Milliseconds from A timestamp(3) Field
Previous:From: Richard Broersma JrDate: 2006-10-17 15:34:45
Subject: Re: Subquery in INSERT?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group