Re: planner regression in 8.4 (from 8.1)

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.