Postgres Pro
Downloads
Home   >   mailing lists

Thread: bad planner pick... but why?

bad planner pick... but why?

From
Date:
hi list

please have a look at this two queries:

----------------------------------------

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)

plan:

Hash IN Join  (cost=205.04..14030.40 rows=1 width=4) (actual
time=105.568..277.782 rows=1 loops=1)
  Hash Cond: ("outer".mov_id = "inner".rel_movieid)
  ->  Hash Left Join  (cost=184.00..13824.49 rows=36973 width=4) (actual
time=7.563..260.627 rows=36997 loops=1)
        Hash Cond: ("outer".mov_id = "inner".c_m_id)
        ->  Seq Scan on movies  (cost=0.00..13140.73 rows=36973 width=4)
(actual time=0.070..206.254 rows=36997 loops=1)
        ->  Hash  (cost=174.60..174.60 rows=3760 width=4) (actual
time=7.467..7.467 rows=3760 loops=1)
              ->  Seq Scan on content  (cost=0.00..174.60 rows=3760 width=4)
(actual time=0.015..4.729 rows=3760 loops=1)
  ->  Hash  (cost=21.03..21.03 rows=1 width=4) (actual time=0.113..0.113
rows=1 loops=1)
        ->  Unique  (cost=20.99..21.02 rows=1 width=4) (actual
time=0.108..0.109 rows=1 loops=1)
              ->  Sort  (cost=20.99..21.01 rows=6 width=4) (actual
time=0.106..0.107 rows=1 loops=1)
                    Sort Key: rel_persons.rel_movieid
                    ->  Index Scan using rel_persons_personid_idx on
rel_persons  (cost=0.00..20.92 rows=6 width=4) (actual time=0.094..0.098
rows=1 loops=1)
                          Index Cond: (rel_personid = 40544)
Total runtime: 277.901 ms

----------------------------------------


SELECT mov_id FROM oldtables.movies LEFT JOIN oldtables.content ON
movies.mov_id = content.c_m_id
WHERE c_m_id IN (SELECT DISTINCT rel_movieid FROM infos.rel_persons WHERE
rel_personid = 40544)

plan:

Nested Loop  (cost=20.99..32.69 rows=1 width=4) (actual time=0.169..0.175
rows=1 loops=1)
  ->  Nested Loop  (cost=20.99..27.05 rows=1 width=8) (actual
time=0.158..0.162 rows=1 loops=1)
        ->  Unique  (cost=20.99..21.02 rows=1 width=4) (actual
time=0.114..0.116 rows=1 loops=1)
              ->  Sort  (cost=20.99..21.01 rows=6 width=4) (actual
time=0.113..0.113 rows=1 loops=1)
                    Sort Key: rel_persons.rel_movieid
                    ->  Index Scan using rel_persons_personid_idx on
rel_persons  (cost=0.00..20.92 rows=6 width=4) (actual time=0.098..0.102
rows=1 loops=1)
                          Index Cond: (rel_personid = 40544)
        ->  Index Scan using movies_mov_id_idx on movies  (cost=0.00..6.00
rows=1 width=4) (actual time=0.039..0.040 rows=1 loops=1)
              Index Cond: ("outer".rel_movieid = movies.mov_id)
  ->  Index Scan using content_pkey on content  (cost=0.00..5.63 rows=1
width=4) (actual time=0.008..0.009 rows=1 loops=1)
        Index Cond: ("outer".mov_id = content.c_m_id)
Total runtime: 0.295 ms

----------------------------------------

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?

here's the plan for the IN (...) subselect:

----------------------------------------

SELECT DISTINCT rel_movieid FROM infos.rel_persons WHERE rel_personid =
40544

plan:
Unique  (cost=20.99..21.02 rows=1 width=4) (actual time=0.112..0.113 rows=1
loops=1)
  ->  Sort  (cost=20.99..21.01 rows=6 width=4) (actual time=0.110..0.110
rows=1 loops=1)
        Sort Key: rel_movieid
        ->  Index Scan using rel_persons_personid_idx on rel_persons
(cost=0.00..20.92 rows=6 width=4) (actual time=0.095..0.098 rows=1 loops=1)
              Index Cond: (rel_personid = 40544)
Total runtime: 0.155 ms

----------------------------------------

its pgsql 8.1

thanks,
thomas

Re: bad planner pick... but why?

From
Tom Lane
Date:
<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

Re: bad planner pick... but why?

From
Date:
>> 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

Re: bad planner pick... but why?

From
Date:
> 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
>