Re: Bogus nestloop rows estimate in 8.4.7

On Mon, May 28, 2012 at 11:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> However, the error in your original example is far too large to be
> explained by that, so I think it was tripping over something different.

Good point. But I generated a bigger data set with the above test case
and it gets progressively worse with more rows and partitions. (The
original database has 2x4 billion rows in over 100 partitions)

Here's a bigger test case, 2GB total (will take a few minutes to
generate). It gives a total estimate of 3900158, even though the
Append nodes suggest 13x2406 rows.

create table a_parent (i int);
create table a_child1 () inherits (a_parent);
create table a_child2 () inherits (a_parent);
create table a_child3 () inherits (a_parent);
insert into a_child1 select generate_series(00000001,10000000);
insert into a_child2 select generate_series(10000001,20000000);
insert into a_child3 select generate_series(20000001,30000000);
create index a1_i_idx on a_child1(i);
create index a2_i_idx on a_child2(i);
create index a3_i_idx on a_child3(i);
alter table a_child1 add check (i between 00000001 and 10000000);
alter table a_child2 add check (i between 10000001 and 20000000);
alter table a_child3 add check (i between 20000001 and 30000000);

create table b_parent (i int);
create table b_child1 () inherits (b_parent);
create table b_child2 () inherits (b_parent);
create table b_child3 () inherits (b_parent);
insert into b_child1 select generate_series(00000001,10000000);
insert into b_child1 select generate_series(00000001,10000000);
insert into b_child2 select generate_series(10000001,20000000);
insert into b_child2 select generate_series(10000001,20000000);
insert into b_child3 select generate_series(20000001,30000000);
insert into b_child3 select generate_series(20000001,30000000);
create index b1_i_idx on b_child1(i);
create index b2_i_idx on b_child2(i);
create index b3_i_idx on b_child3(i);
alter table b_child1 add check (i between 00000001 and 10000000);
alter table b_child2 add check (i between 10000001 and 20000000);
alter table b_child3 add check (i between 20000001 and 30000000);

analyze;
explain select * from a_parent join b_parent using (i) where i between 1 and 2;

Nested Loop  (cost=0.00..1413.71 rows=3900158 width=4) Join Filter: (public.a_parent.i = public.b_parent.i) ->  Append
(cost=0.00..55.37rows=13 width=4)       ->  Seq Scan on a_parent  (cost=0.00..46.00 rows=12 width=4)
Filter:((i >= 1) AND (i <= 2))       ->  Index Scan using a1_i_idx on a_child1 a_parent
 
(cost=0.00..9.37 rows=1 width=4)             Index Cond: ((i >= 1) AND (i <= 2)) ->  Append  (cost=0.00..74.41
rows=2406width=4)       ->  Seq Scan on b_parent  (cost=0.00..34.00 rows=2400 width=4)       ->  Index Scan using
b1_i_idxon b_child1 b_parent
 
(cost=0.00..13.43 rows=2 width=4)             Index Cond: (public.b_parent.i = public.a_parent.i)       ->  Index Scan
usingb2_i_idx on b_child2 b_parent
 
(cost=0.00..13.50 rows=2 width=4)             Index Cond: (public.b_parent.i = public.a_parent.i)       ->  Index Scan
usingb3_i_idx on b_child3 b_parent
 
(cost=0.00..13.48 rows=2 width=4)             Index Cond: (public.b_parent.i = public.a_parent.i)

Regards,
Marti