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

Re: Bogus nestloop rows estimate in 8.4.7

From: Marti Raudsepp <marti(at)juffo(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Bogus nestloop rows estimate in 8.4.7
Date: 2012-05-28 19:32:49
Message-ID: CABRT9RD9EuNw-P_Pqp9YNUjA+PrChZT3trNJTn+zmhbwE0POnw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mon, May 28, 2012 at 8:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Also, what do you have constraint_exclusion set to?

The only sane value, "partition"

> This sounds familiar, but a quick trawl through the commit logs didn't
> immediately turn up any related-looking patches.  Can you put together
> a self-contained test case?

Sure, tested on 8.4.7, 8.4.11,  with all default configuration. Does
not occur in >=9.0

create table a_parent (i int);
create table a_child1 () inherits (a_parent);
create table a_child2 () inherits (a_parent);
insert into a_child1 select generate_series(1,100000);
insert into a_child2 select generate_series(100001,200000);
create index a1_i_idx on a_child1(i);
create index a2_i_idx on a_child2(i);

create table b_parent (i int);
create table b_child1 () inherits (b_parent);
create table b_child2 () inherits (b_parent);
insert into b_child1 select generate_series(1,100000);
insert into b_child1 select generate_series(1,100000);
insert into b_child2 select generate_series(100001,200000);
insert into b_child2 select generate_series(100001,200000);
create index b1_i_idx on b_child1(i);
create index b2_i_idx on b_child2(i);

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

Actually returns 4 rows, but estimate is 28168

QUERY PLAN
Nested Loop  (cost=0.00..1276.16 rows=28168 width=4)
  Join Filter: (public.a_parent.i = public.b_parent.i)
  ->  Append  (cost=0.00..62.56 rows=14 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..8.28 rows=1 width=4)
              Index Cond: ((i >= 1) AND (i <= 2))
        ->  Index Scan using a2_i_idx on a_child2 a_parent
(cost=0.00..8.28 rows=1 width=4)
              Index Cond: ((i >= 1) AND (i <= 2))
  ->  Append  (cost=0.00..56.64 rows=2404 width=4)
        ->  Seq Scan on b_parent  (cost=0.00..34.00 rows=2400 width=4)
        ->  Index Scan using b2_i_idx on b_child2 b_parent
(cost=0.00..11.31 rows=2 width=4)
              Index Cond: (public.b_parent.i = public.a_parent.i)
        ->  Index Scan using b1_i_idx on b_child1 b_parent
(cost=0.00..11.32 rows=2 width=4)
              Index Cond: (public.b_parent.i = public.a_parent.i)
(15 rows)


There was a similar case in 9.0.4 with WHERE i=1, but that has been
fixed in 9.0.7

Regards,
Marti

In response to

Responses

pgsql-hackers by date

Next:From: Marti RaudseppDate: 2012-05-28 19:37:01
Subject: Re: Bogus nestloop rows estimate in 8.4.7
Previous:From: Tom LaneDate: 2012-05-28 19:11:20
Subject: Upcoming back-branch PG releases

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