Re: PG choosing nested loop for set membership?

Brian Crowell <brian(at)fluggo(dot)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=158 width=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