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

Re: planner regression in 8.4 (from 8.1)

From: Ben Chobot <bench(at)silentmedia(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: planner regression in 8.4 (from 8.1)
Date: 2010-02-21 16:08:31
Message-ID: AA5212B3-53A0-4041-A166-1EDF4F9C7931@silentmedia.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Feb 21, 2010, at 4:28 AM, Robert Haas wrote:

> On Wed, Feb 17, 2010 at 1:06 PM, Ben Chobot <bench(at)silentmedia(dot)com> wrote:
>>         ->  Hash  (cost=153.63..153.63 rows=2178408 width=4) (actual time=0.207..0.207 rows=1 loops=1)
>>               ->  Nested Loop  (cost=4.58..153.63 rows=2178408 width=4) (actual time=0.203..0.204 rows=1 loops=1)
>>                     ->  HashAggregate  (cost=4.58..4.59 rows=1 width=4) (actual time=0.145..0.146 rows=1 loops=1)
>>                           ->  Nested Loop  (cost=2.28..4.57 rows=1 width=4) (actual time=0.142..0.143 rows=1 loops=1)
>>                                 ->  HashAggregate  (cost=2.28..2.29 rows=1 width=4) (actual time=0.093..0.093 rows=1 loops=1)
>>                                       ->  Index Scan using pro_partners_tree_sortkey_idx on pro_partners  (cost=0.00..2.28 rows=1 width=4) (actual time=0.076..0.076 rows=1 loops=1)
>>                                             Index Cond: ((tree_sortkey >= B'000000000000000110000000000000001111010011011010'::bit varying) AND (tree_sortkey <= B'00000000000000011000000000000000111101001101101011111111111111111111111111111111'::bit varying))
>>                                 ->  Index Scan using user_groups_pro_partner_id_idx on user_groups  (cost=0.00..2.27 rows=1 width=8) (actual time=0.046..0.047 rows=1 loops=1)
>>                                       Index Cond: (user_groups.pro_partner_id = pro_partners.id)
>>                     ->  Index Scan using users_user_groups_idx on users  (cost=0.00..147.14 rows=152 width=8) (actual time=0.057..0.057 rows=1 loops=1)
>>                           Index Cond: (users.user_group_id = user_groups.id)
>>                           Filter: (NOT users.deleted)
> [...]
>> 
>> Note the nested loop with 2 million expected rows, though its inner nodes
>> are only expected to have 1 and 152 each.
> 
> As you say, this is the part that looks pretty weird.  I *think* that
> the number of rows for the nestloop is being set by
> set_joinrel_size_estimates() by this line of code:
> 
>            nrows = outer_rel->rows * inner_rel->rows * jselec;
> 
> That seems like it implies a ridiculously large value for jselec, but jselec is:
> 
>        jselec = clauselist_selectivity(root,
>                                        restrictlist,
>                                        0,
>                                        jointype,
>                                        sjinfo);
> 
> ...and I don't really see how that can turn out to be anything too crazy.
> 
> Is there any chance you can extract a reproducible test case for this
> problem that doesn't involve your private data?
> 

I'll try, though honestly finding the time to do it might take a while. In the meantime, if you have things to check I'm happy to run some queries and report the results.



In response to

pgsql-bugs by date

Next:From: Toni HeleniusDate: 2010-02-22 11:03:44
Subject: BUG #5338: PG_DUMP fails due to invalid adnum value
Previous:From: Robert HaasDate: 2010-02-21 12:28:48
Subject: Re: planner regression in 8.4 (from 8.1)

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