Re: planner regression in 8.4 (from 8.1)

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?

...Robert