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: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Pg Bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #6335: Weird planner decision with exists (a join b) condition
Date: 2011-12-15 00:53:22
Message-ID: CAK-MWwTNx1DCxuJsXakY-0czYdnn2Rq=ArJZqCcK7n5DVSgD7g@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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
all of them affected by the problem:

select version();
drop table if exists test1;
drop table if exists test2;
drop table if exists test3;
CREATE TABLE test1 AS SELECT user_id FROM generate_series(1,1000000) AS
gs(user_id);
CREATE TABLE test2 AS SELECT user_id,user_id AS resume_id FROM
generate_series(1,1000000) AS gs(user_id);
CREATE TABLE test3 AS SELECT user_id AS resume_id FROM
generate_series(1,1000000) AS gs(user_id);
create index test1_user_id_key on test1(user_id);
create index test2_user_id_key on test2(user_id);
create index test2_resume_id_key on test2(resume_id);
create index test3_resume_id_key on test3(resume_id);
analyze test1;
analyze test2;
analyze test3;
--good
EXPLAIN ANALYZE  select *
from test1
where
test1.user_id in (100)
and exists (
SELECT * from test2
join test3 using (resume_id)
where
test2.user_id = test1.user_id
);
--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
);



On Thu, Dec 15, 2011 at 1:00 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com>wrote:

>
> Excerpts from maxim.boguk's message of mié dic 14 08:09:38 -0300 2011:
>
> > But once I add second value into IN list plan become completely screwed:
>
> See here:
>
> http://archives.postgresql.org/message-id/1309918036-sup-4092@alvh.no-ip.org
> Perhaps it's a similar problem.
>
> Maybe you'd get enthused enough to try to fix the problem?
>
> --
> Álvaro Herrera <alvherre(at)commandprompt(dot)com>
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>



-- 
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

Responses

pgsql-bugs by date

Next:From: bricklenDate: 2011-12-15 01:00:50
Subject: Re: BUG #6335: Weird planner decision with exists (a join b) condition
Previous:From: Alvaro HerreraDate: 2011-12-14 22:06:23
Subject: Re: BUG #6334: initdb not working

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