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

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

From: Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com>
To: bricklen <bricklen(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6335: Weird planner decision with exists (a join b) condition
Date: 2011-12-15 01:28:13
Message-ID: CAK-MWwShftpV7TZL7dx73ttNENCmwZ_TE1Y_TRhVJ9TkoCGKgw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Thu, Dec 15, 2011 at 12:00 PM, bricklen <bricklen(at)gmail(dot)com> wrote:

> On Wed, Dec 14, 2011 at 4:53 PM, Maxim Boguk <maxim(dot)boguk(at)gmail(dot)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 = 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=3.78..91844.51 rows=2 width=4) (actual
time=0.634..0.750 rows=2 loops=1)
   Join Filter: (test1.user_id = test2.user_id)
   ->  Bitmap Heap Scan on test1  (cost=2.54..4.57 rows=2 width=4) (actual
time=0.013..0.015 rows=2 loops=1)
         Recheck Cond: (user_id = ANY ('{100,101}'::integer[]))
         ->  Bitmap Index Scan on test1_user_id_key  (cost=0.00..2.54
rows=2 width=0) (actual time=0.009..0.009 rows=2 loops=1)
               Index Cond: (user_id = ANY ('{100,101}'::integer[]))
   ->  Materialize  (cost=1.23..64339.94 rows=1000000 width=4) (actual
time=0.018..0.305 rows=100 loops=2)
         ->  Merge Join  (cost=1.23..59339.94 rows=1000000 width=4) (actual
time=0.032..0.446 rows=101 loops=1)
               Merge Cond: (test2.resume_id = test3.resume_id)
               ->  Index Scan using test2_resume_id_key on test2
(cost=0.00..22170.28 rows=1000000 width=8) (actual time=0.009..0.076
rows=101 loops=1)
               ->  Index Scan using test3_resume_id_key on test3
(cost=0.00..22170.28 rows=1000000 width=4) (actual time=0.007..0.075
rows=101 loops=1)
 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 = test1.user_id
);


QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Semi Join  (cost=3.78..91844.51 rows=2 width=4) (actual
time=432.266..4457.799 rows=2 loops=1)
   Join Filter: (test1.user_id = test2.user_id)
   ->  Bitmap Heap Scan on test1  (cost=2.54..4.57 rows=2 width=4) (actual
time=0.057..0.063 rows=2 loops=1)
         Recheck Cond: (user_id = ANY ('{90000,900001}'::integer[]))
         ->  Bitmap Index Scan on test1_user_id_key  (cost=0.00..2.54
rows=2 width=0) (actual time=0.050..0.050 rows=2 loops=1)
               Index Cond: (user_id = ANY ('{90000,900001}'::integer[]))
   ->  Materialize  (cost=1.23..64339.94 rows=1000000 width=4) (actual
time=0.011..1942.046 rows=495000 loops=2)
         ->  Merge Join  (cost=1.23..59339.94 rows=1000000 width=4) (actual
time=0.018..2805.842 rows=900001 loops=1)
               Merge Cond: (test2.resume_id = test3.resume_id)
               ->  Index Scan using test2_resume_id_key on test2
(cost=0.00..22170.28 rows=1000000 width=8) (actual time=0.007..571.851
rows=900001 loops=1)
               ->  Index Scan using test3_resume_id_key on test3
(cost=0.00..22170.28 rows=1000000 width=4) (actual time=0.006..594.484
rows=900001 loops=1)
 Total runtime: 4467.887 ms


-- 
Maxim Boguk
Senior Postgresql DBA.

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

Skype: maxim.boguk
Jabber: maxim(dot)boguk(at)gmail(dot)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?

МойКруг: http://mboguk.moikrug.ru/
Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не
все.

In response to

pgsql-bugs by date

Next:From: Kevin GrittnerDate: 2011-12-15 01:47:46
Subject: Re: BUG #6336: SQL stored procedure returing 'int' calling into SRF does not raise error ...
Previous:From: bricklenDate: 2011-12-15 01:00:50
Subject: Re: BUG #6335: Weird planner decision with exists (a join b) condition

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