BUG #6359: excessively inlining subquery leads to slow queries

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.