Jim Crate <jimcfl(at)gmail(dot)com> writes:
> My question is why does it do a seq scan when it flattens this
> subquery into a JOIN?
Because it thinks there will be 3783 rows out of the msg scan, which if
true would make your desired nestloop join a serious loser. You need to
see about getting that estimate to be off by less than three orders of
magnitude. Possibly raising the stats target on emsg_messages would
help. I'd also try converting the inner NOT IN into a NOT EXISTS, just
to see if that makes the estimate any better. Using something newer
than 9.0.2 might help too, as we fixed some outer-join estimation bugs a
few months ago.
regards, tom lane