On Feb 21, 2010, at 4:28 AM, Robert Haas wrote: > 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? >
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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
