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

BUG #6359: excessively inlining subquery leads to slow queries

From: maxim(dot)boguk(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6359: excessively inlining subquery leads to slow queries
Date: 2011-12-26 13:50:49
Message-ID: E1RfAwz-0006Us-7B@wrigleys.postgresql.org (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-bugs
The following bug has been logged on the website:

Bug reference:      6359
Logged by:          Maksym Boguk
Email address:      maxim(dot)boguk(at)gmail(dot)com
PostgreSQL version: 9.1.2
Operating system:   Ubuntu linux
Description:        

Sometime Postgres inline subrequest even if it produce slower plan (and that
slow plan have higher actual cost than non-inlined plan):

test case:

drop table if exists t1;
drop table if exists t2;

create table t1 as select id from generate_series(1,1) as g(id);
create table t2 as select id from generate_series(1,1000) as g(id);
alter table t1 add primary key (id);
alter table t2 add primary key (id);


analyze t1;
analyze t2;

--fast non-inlined plan
explain (verbose, analyze)
select
    id,
   
t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id
FROM
(
   select  t1.id,
           (select t2.id from t2 where t2.id=t1.id) as t2_id
   from t1
   offset 0
) as t;

                                                                            
                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on t  (cost=0.00..0.65 rows=1 width=8) (actual
time=0.066..0.069 rows=1 loops=1)
   Output: t.id, ((((((((((((((((t.t2_id + t.t2_id) + t.t2_id) + t.t2_id) +
t.t2_id) + t.t2_id) + t.t2_id) + t.t2_id) + t.t2_id) + t.t2_id) + t.t2_id) +
t.t2_id) + t.t2_id) + t.t2_id) + t.t2_id) + t.t2_id) + t.t2_id)
   ->  Limit  (cost=0.00..0.60 rows=1 width=4) (actual time=0.053..0.056
rows=1 loops=1)
         Output: t1.id, ((SubPlan 1))
         ->  Seq Scan on public.t1  (cost=0.00..0.60 rows=1 width=4) (actual
time=0.052..0.053 rows=1 loops=1)
               Output: t1.id, (SubPlan 1)
               SubPlan 1
                 ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49
rows=1 width=4) (actual time=0.025..0.028 rows=1 loops=1)
                       Output: t2.id
                       Index Cond: (t2.id = t1.id)
 Total runtime: 0.161 ms
(11 rows)



--slow inlined plan
explain (verbose, analyze)
select
    id,
   
t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id+t2_id
FROM
(
   select  t1.id,
           (select t2.id from t2 where t2.id=t1.id) as t2_id
   from t1
--   offset 0
) as t;

                                                                            
                                                                 QUERY PLAN 
                                                                            
                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.t1  (cost=0.00..8.44 rows=1 width=4) (actual
time=0.180..0.181 rows=1 loops=1)
   Output: t1.id, (((((((((((((((((SubPlan 1) + (SubPlan 2)) + (SubPlan 3))
+ (SubPlan 4)) + (SubPlan 5)) + (SubPlan 6)) + (SubPlan 7)) + (SubPlan 8)) +
(SubPlan 9)) + (SubPlan 10)) + (SubPlan 11)) + (SubPlan 12)) + (SubPlan 13))
+ (SubPlan 14)) + (SubPlan 15)) + (SubPlan 16)) + (SubPlan 17))
   SubPlan 1
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.025..0.028 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
   SubPlan 2
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.006..0.007 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
   SubPlan 3
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.005..0.006 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
   SubPlan 4
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.004..0.005 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
   SubPlan 5
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.004..0.004 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
   SubPlan 6
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.004..0.004 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
   SubPlan 7
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.004..0.004 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
   SubPlan 8
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.004..0.005 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
   SubPlan 9
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.004..0.005 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
   SubPlan 10
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.004..0.005 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
   SubPlan 11
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.004..0.005 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
   SubPlan 12
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.004..0.005 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
   SubPlan 13
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.005..0.006 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
   SubPlan 14
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.005..0.006 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
   SubPlan 15
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.005..0.006 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
   SubPlan 16
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.004..0.005 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
   SubPlan 17
     ->  Index Scan using t2_pkey on public.t2  (cost=0.00..0.49 rows=1
width=4) (actual time=0.005..0.006 rows=1 loops=1)
           Output: public.t2.id
           Index Cond: (public.t2.id = t1.id)
 Total runtime: 0.466 ms
(71 rows)

The inlined plan uses 3x more time and have 10x higher cost.

I found that problem in much more longer analytical query where subrequest
is slow and complicated.


Responses

pgsql-bugs by date

Next:From: Devrim GÜNDÜZDate: 2011-12-26 14:41:44
Subject: Re: BUG #6341: Packaging - virtual provides "postgres" without version
Previous:From: thiagoliveiracorreiaDate: 2011-12-25 19:29:00
Subject: BUG #6358: [bug] pgAdmin não abre script sq?==?utf-8?q?l das tabelas

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