On Wed, Feb 17, 2010 at 1:06 PM, Ben Chobot <[email protected]> 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
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
