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