strange plan - PostgreSQL 9.2

Lists: pgsql-hackers
From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: strange plan - PostgreSQL 9.2
Date: 2012-02-28 16:49:11
Message-ID: CAFj8pRC4eoF0ue=4hhp0bVmVCjY_hoPOKTZv3AwNJGzARA=XVA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hello

I try to look on one slow query with correlated subquery:

create table xx(a int primary key);
create table yy(a int);

insert into xx select generate_series(1,1000000);
insert into yy select (random()*1000000)::int from generate_series(1,100000);

create index on yy(a);

Query A
select a, (select true from yy where xx.a = yy.a limit 1) from xx
limit 10 offset 0;

postgres=> explain select a, (select true from yy where xx.a = yy.a
limit 1) from xx;
QUERY PLAN
--------------------------------------------------------------------------------------
Seq Scan on xx (cost=0.00..4392325.00 rows=1000000 width=4)
SubPlan 1
-> Limit (cost=0.00..4.38 rows=1 width=0)
-> Index Only Scan using yy_a_idx on yy (cost=0.00..4.38
rows=1 width=0)
Index Cond: (a = xx.a)
(5 rows)

plan for this query is expected

But when I rewrote query I got strange plan (query B):

postgres=> explain select a, exists(select 1 from yy where xx.a =
yy.a) from xx limit 10 offset 0;
QUERY PLAN
--------------------------------------------------------------------------------------
Limit (cost=0.00..43.92 rows=10 width=4)
-> Seq Scan on xx (cost=0.00..4392325.00 rows=1000000 width=4)
SubPlan 1
-> Index Only Scan using yy_a_idx on yy (cost=0.00..4.38
rows=1 width=0)
Index Cond: (a = xx.a)
SubPlan 2
-> Seq Scan on yy (cost=0.00..1443.00 rows=100000 width=4)
(7 rows)

Why there are a SubPlan 2?

But query B is two time faster than query A

public | xx | table | pavel | 35 MB |
public | yy | table | pavel | 3576 kB |

regards

Pavel


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: strange plan - PostgreSQL 9.2
Date: 2012-02-28 21:01:39
Message-ID: 11031.1330462899@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> Why there are a SubPlan 2?

http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=bd3daddaf232d95b0c9ba6f99b0170a0147dd8af

regards, tom lane


From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: strange plan - PostgreSQL 9.2
Date: 2012-02-28 21:20:54
Message-ID: CAFj8pRDKuvtsZVoFoP6N6WegVtXcOC=XdiMCVw=Yrrt+=sT7Xg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

2012/2/28 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> Why there are a SubPlan 2?
>
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=bd3daddaf232d95b0c9ba6f99b0170a0147dd8af
>
>                        regards, tom lane

Thank you - I can verify so it works well, but a EXPLAIN result is
really strange

Pavel