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@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)
> [...]
>>=20
>> Note the nested loop with 2 million expected rows, though its inner nodes
>> are only expected to have 1 and 152 each.
>=20
> 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:
>=20
>            nrows =3D outer_rel->rows * inner_rel->rows * jselec;
>=20
> That seems like it implies a ridiculously large value for jselec, but jse=
lec is:
>=20
>        jselec =3D clauselist_selectivity(root,
>                                        restrictlist,
>                                        0,
>                                        jointype,
>                                        sjinfo);
>=20
> ...and I don't really see how that can turn out to be anything too crazy.
>=20
> Is there any chance you can extract a reproducible test case for this
> problem that doesn't involve your private data?
>=20

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.