Re: PG choosing nested loop for set membership?

Brian Crowell <brian@fluggo.com> writes:
> Explaining just this view yields:

> 'Nested Loop  (cost=2.77..10.23 rows=2 width=10) (actual time=0.086..0.222 rows=241 loops=1)'
> '  ->  Hash Right Join  (cost=2.62..5.12 rows=1 width=8) (actual time=0.064..0.068 rows=1 loops=1)'
> '  ->  Index Scan using _visible_accounts_by_rule_set_idx on pl2._visible_accounts_by_rule_set acc  (cost=0.15..3.54
rows=158width=14) (actual time=0.018..0.086 rows=241 loops=1)' 

> All of the estimates on this view are reasonable, except for that
> nested loop at the top.

Yeah.  The weird thing about that is that the nestloop rowcount estimate
isn't the product of the two input rowcounts --- you'd sort of expect an
estimate of 158 given the input-relation sizes.  While that's not ipso
facto evidence of a bug (because the estimates are arrived at in different
ways), I'm having a hard time replicating it here.  Are you using an
up-to-date PG release?

One thing that might help is to increase the statistics target for
pl2._visible_accounts_by_rule_set.  The other two tables are small enough
that you don't need to do that for them.  (Although come to think of it,
they are also small enough that maybe auto-analyze isn't triggering for
them ... does a manual ANALYZE improve matters?)

            regards, tom lane