BUG #6359: excessively inlining subquery leads to slow queries

Поиск
Список
Период
Сортировка
От maxim.boguk@gmail.com
Тема BUG #6359: excessively inlining subquery leads to slow queries
Дата
Msg-id E1RfAwz-0006Us-7B@wrigleys.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #6359: excessively inlining subquery leads to slow queries  (Robert Haas <robertmhaas@gmail.com>)
Список pgsql-bugs
Дерево обсуждения 
The following bug has been logged on the website:

Bug reference:      6359
Logged by:          Maksym Boguk
Email address:      maxim.boguk@gmail.com
PostgreSQL version: 9.1.2
Operating system:   Ubuntu linux
Description:=20=20=20=20=20=20=20=20

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,
=20=20=20
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=3Dt1.id) as t2_id
   from t1
   offset 0
) as t;

=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20
                          QUERY PLAN
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-------------------------------------------------------------------
 Subquery Scan on t  (cost=3D0.00..0.65 rows=3D1 width=3D8) (actual
time=3D0.066..0.069 rows=3D1 loops=3D1)
   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=3D0.00..0.60 rows=3D1 width=3D4) (actual time=3D0.053..=
0.056
rows=3D1 loops=3D1)
         Output: t1.id, ((SubPlan 1))
         ->  Seq Scan on public.t1  (cost=3D0.00..0.60 rows=3D1 width=3D4) =
(actual
time=3D0.052..0.053 rows=3D1 loops=3D1)
               Output: t1.id, (SubPlan 1)
               SubPlan 1
                 ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0=
.49
rows=3D1 width=3D4) (actual time=3D0.025..0.028 rows=3D1 loops=3D1)
                       Output: t2.id
                       Index Cond: (t2.id =3D t1.id)
 Total runtime: 0.161 ms
(11 rows)



--slow inlined plan
explain (verbose, analyze)
select
    id,
=20=20=20
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=3Dt1.id) as t2_id
   from t1
--   offset 0
) as t;

=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20
                                                                 QUERY PLAN=
=20
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------
 Seq Scan on public.t1  (cost=3D0.00..8.44 rows=3D1 width=3D4) (actual
time=3D0.180..0.181 rows=3D1 loops=3D1)
   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=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.025..0.028 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D t1.id)
   SubPlan 2
     ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.006..0.007 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D t1.id)
   SubPlan 3
     ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.005..0.006 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D t1.id)
   SubPlan 4
     ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.004..0.005 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D t1.id)
   SubPlan 5
     ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.004..0.004 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D t1.id)
   SubPlan 6
     ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.004..0.004 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D t1.id)
   SubPlan 7
     ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.004..0.004 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D t1.id)
   SubPlan 8
     ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.004..0.005 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D t1.id)
   SubPlan 9
     ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.004..0.005 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D t1.id)
   SubPlan 10
     ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.004..0.005 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D t1.id)
   SubPlan 11
     ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.004..0.005 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D t1.id)
   SubPlan 12
     ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.004..0.005 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D t1.id)
   SubPlan 13
     ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.005..0.006 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D t1.id)
   SubPlan 14
     ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.005..0.006 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D t1.id)
   SubPlan 15
     ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.005..0.006 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D t1.id)
   SubPlan 16
     ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.004..0.005 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D t1.id)
   SubPlan 17
     ->  Index Scan using t2_pkey on public.t2  (cost=3D0.00..0.49 rows=3D1
width=3D4) (actual time=3D0.005..0.006 rows=3D1 loops=3D1)
           Output: public.t2.id
           Index Cond: (public.t2.id =3D 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.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: thiagoliveiracorreia@yahoo.com.br
Дата:
Сообщение: BUG #6358: [bug] pgAdmin não abre script sql das tabelas
Следующее
От: Devrim GÜNDÜZ
Дата:
Сообщение: Re: BUG #6341: Packaging - virtual provides "postgres" without version