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

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/
Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не
все.