Re: planner regression in 8.4 (from 8.1)

On Wed, Feb 17, 2010 at 1:06 PM, Ben Chobot <bench@silentmedia.com> wrote:
>          ->  Hash  (cost=3D153.63..153.63 rows=3D2178408 width=3D4) (actu=
al time=3D0.207..0.207 rows=3D1 loops=3D1)
>                ->  Nested Loop  (cost=3D4.58..153.63 rows=3D2178408 width=
=3D4) (actual time=3D0.203..0.204 rows=3D1 loops=3D1)
>                      ->  HashAggregate  (cost=3D4.58..4.59 rows=3D1 width=
=3D4) (actual time=3D0.145..0.146 rows=3D1 loops=3D1)
>                            ->  Nested Loop  (cost=3D2.28..4.57 rows=3D1 w=
idth=3D4) (actual time=3D0.142..0.143 rows=3D1 loops=3D1)
>                                  ->  HashAggregate  (cost=3D2.28..2.29 ro=
ws=3D1 width=3D4) (actual time=3D0.093..0.093 rows=3D1 loops=3D1)
>                                        ->  Index Scan using pro_partners_=
tree_sortkey_idx on pro_partners  (cost=3D0.00..2.28 rows=3D1 width=3D4) (a=
ctual time=3D0.076..0.076 rows=3D1 loops=3D1)
>                                              Index Cond: ((tree_sortkey >=
=3D B'000000000000000110000000000000001111010011011010'::bit varying) AND (=
tree_sortkey <=3D B'0000000000000001100000000000000011110100110110101111111=
1111111111111111111111111'::bit varying))
>                                  ->  Index Scan using user_groups_pro_par=
tner_id_idx on user_groups  (cost=3D0.00..2.27 rows=3D1 width=3D8) (actual =
time=3D0.046..0.047 rows=3D1 loops=3D1)
>                                        Index Cond: (user_groups.pro_partn=
er_id =3D pro_partners.id)
>                      ->  Index Scan using users_user_groups_idx on users =
 (cost=3D0.00..147.14 rows=3D152 width=3D8) (actual time=3D0.057..0.057 row=
s=3D1 loops=3D1)
>                            Index Cond: (users.user_group_id =3D user_grou=
ps.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 =3D outer_rel->rows * inner_rel->rows * jselec;

That seems like it implies a ridiculously large value for jselec, but jsele=
c is:

        jselec =3D 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