Re: BUG #6335: Weird planner decision with exists (a join b) condition

On Thu, Dec 15, 2011 at 12:00 PM, bricklen <bricklen@gmail.com> wrote:

> On Wed, Dec 14, 2011 at 4:53 PM, Maxim Boguk <maxim.boguk@gmail.com>
> wrote:
> > Here goes self-contained test case.
> >
> > I tested it on the 9.1.2, 9.1.1, 9.0.5, 9.0.4, 8.4.7
>
> I just tested on 9.1.2 and see the same issue.
>
> > --bad
> > EXPLAIN ANALYZE  select *
> > from test1
> > where
> > test1.user_id in (100, 101)
> > and exists (
> > SELECT * from test2
> > join test3 using (resume_id)
> > where
> > test2.user_id =3D test1.user_id
> > );
>
> Setting enable_hashjoin to false pushes it back to a good plan again.
>

Could you show explain analyze of the good plan please?

If you getting plan like:

QUERY PLAN
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---
 Nested Loop Semi Join  (cost=3D3.78..91844.51 rows=3D2 width=3D4) (actual
time=3D0.634..0.750 rows=3D2 loops=3D1)
   Join Filter: (test1.user_id =3D test2.user_id)
   ->  Bitmap Heap Scan on test1  (cost=3D2.54..4.57 rows=3D2 width=3D4) (a=
ctual
time=3D0.013..0.015 rows=3D2 loops=3D1)
         Recheck Cond: (user_id =3D ANY ('{100,101}'::integer[]))
         ->  Bitmap Index Scan on test1_user_id_key  (cost=3D0.00..2.54
rows=3D2 width=3D0) (actual time=3D0.009..0.009 rows=3D2 loops=3D1)
               Index Cond: (user_id =3D ANY ('{100,101}'::integer[]))
   ->  Materialize  (cost=3D1.23..64339.94 rows=3D1000000 width=3D4) (actual
time=3D0.018..0.305 rows=3D100 loops=3D2)
         ->  Merge Join  (cost=3D1.23..59339.94 rows=3D1000000 width=3D4) (=
actual
time=3D0.032..0.446 rows=3D101 loops=3D1)
               Merge Cond: (test2.resume_id =3D test3.resume_id)
               ->  Index Scan using test2_resume_id_key on test2
(cost=3D0.00..22170.28 rows=3D1000000 width=3D8) (actual time=3D0.009..0.076
rows=3D101 loops=3D1)
               ->  Index Scan using test3_resume_id_key on test3
(cost=3D0.00..22170.28 rows=3D1000000 width=3D4) (actual time=3D0.007..0.075
rows=3D101 loops=3D1)
 Total runtime: 0.785 ms

Try use high values for the user_id :

EXPLAIN ANALYZE  select *
from test1
where
test1.user_id in (90000, 900001)
and exists (
SELECT * from test2
join test3 using (resume_id)
where
test2.user_id =3D test1.user_id
);


QUERY PLAN
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------
 Nested Loop Semi Join  (cost=3D3.78..91844.51 rows=3D2 width=3D4) (actual
time=3D432.266..4457.799 rows=3D2 loops=3D1)
   Join Filter: (test1.user_id =3D test2.user_id)
   ->  Bitmap Heap Scan on test1  (cost=3D2.54..4.57 rows=3D2 width=3D4) (a=
ctual
time=3D0.057..0.063 rows=3D2 loops=3D1)
         Recheck Cond: (user_id =3D ANY ('{90000,900001}'::integer[]))
         ->  Bitmap Index Scan on test1_user_id_key  (cost=3D0.00..2.54
rows=3D2 width=3D0) (actual time=3D0.050..0.050 rows=3D2 loops=3D1)
               Index Cond: (user_id =3D ANY ('{90000,900001}'::integer[]))
   ->  Materialize  (cost=3D1.23..64339.94 rows=3D1000000 width=3D4) (actual
time=3D0.011..1942.046 rows=3D495000 loops=3D2)
         ->  Merge Join  (cost=3D1.23..59339.94 rows=3D1000000 width=3D4) (=
actual
time=3D0.018..2805.842 rows=3D900001 loops=3D1)
               Merge Cond: (test2.resume_id =3D test3.resume_id)
               ->  Index Scan using test2_resume_id_key on test2
(cost=3D0.00..22170.28 rows=3D1000000 width=3D8) (actual time=3D0.007..571.=
851
rows=3D900001 loops=3D1)
               ->  Index Scan using test3_resume_id_key on test3
(cost=3D0.00..22170.28 rows=3D1000000 width=3D4) (actual time=3D0.006..594.=
484
rows=3D900001 loops=3D1)
 Total runtime: 4467.887 ms


--=20
Maxim Boguk
Senior Postgresql DBA.

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

Skype: maxim.boguk
Jabber: maxim.boguk@gmail.com

LinkedIn profile: http://nz.linkedin.com/in/maximboguk
If they can send one man to the moon... why can't they send them all?

=D0=9C=D0=BE=D0=B9=D0=9A=D1=80=D1=83=D0=B3: http://mboguk.moikrug.ru/
=D0=A1=D0=B8=D0=BB=D0=B0 =D1=81=D0=BE=D0=BB=D0=BE=D0=BC=D1=83 =D0=BB=D0=BE=
=D0=BC=D0=B8=D1=82, =D0=BD=D0=BE =D0=BD=D0=B5 =D0=B2=D1=81=D0=B5 =D0=B2 =D0=
=BD=D0=B0=D1=88=D0=B5=D0=B9 =D0=B6=D0=B8=D0=B7=D0=BD=D0=B8 - =D1=81=D0=BE=
=D0=BB=D0=BE=D0=BC=D0=B0, =D0=B4=D0=B0 =D0=B8 =D1=81=D0=B8=D0=BB=D0=B0 =D0=
=B4=D0=B0=D0=BB=D0=B5=D0=BA=D0=BE =D0=BD=D0=B5
=D0=B2=D1=81=D0=B5.