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:37:01
Message-ID: CABRT9RCnzAPZ-C3LcQQxj+Z80sxAzpaJ1AUR46+JdNHv4tJ9dw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mon, May 28, 2012 at 10:32 PM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
> There was a similar case in 9.0.4 with WHERE i=1, but that has been
> fixed in 9.0.7

Oh, it's been fixed in 9.0.7, but apparently not in 8.4.11; the empty
parent tables are confusing the estimate:

explain select * from a_parent join b_parent using (i) where i=1;

QUERY PLAN
Nested Loop  (cost=56.57..123.65 rows=224 width=4)
  ->  Append  (cost=0.00..62.60 rows=16 width=4)
        ->  Seq Scan on b_parent  (cost=0.00..40.00 rows=12 width=4)
              Filter: (i = 1)
        ->  Index Scan using b2_i_idx on b_child2 b_parent
(cost=0.00..11.30 rows=2 width=4)
              Index Cond: (i = 1)
        ->  Index Scan using b1_i_idx on b_child1 b_parent
(cost=0.00..11.30 rows=2 width=4)
              Index Cond: (i = 1)
  ->  Materialize  (cost=56.57..56.71 rows=14 width=4)
        ->  Append  (cost=0.00..56.56 rows=14 width=4)
              ->  Seq Scan on a_parent  (cost=0.00..40.00 rows=12 width=4)
                    Filter: (i = 1)
              ->  Index Scan using a1_i_idx on a_child1 a_parent
(cost=0.00..8.28 rows=1 width=4)
                    Index Cond: (i = 1)
              ->  Index Scan using a2_i_idx on a_child2 a_parent
(cost=0.00..8.28 rows=1 width=4)
                    Index Cond: (i = 1)

Regards,
Marti

In response to

Responses

pgsql-hackers by date

Next:From: Alexander KorotkovDate: 2012-05-28 19:42:19
Subject: WIP: 2d-mapping based indexing for ranges
Previous:From: Marti RaudseppDate: 2012-05-28 19:32:49
Subject: Re: Bogus nestloop rows estimate in 8.4.7

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