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

strange plan - PostgreSQL 9.2

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 (view raw or flat)
Thread:
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

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2012-02-28 17:25:13
Subject: Re: Initial 9.2 pgbench write results
Previous:From: Alvaro HerreraDate: 2012-02-28 16:48:35
Subject: Re: review: CHECK FUNCTION statement

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