Jeremy Wells <jemmyw@gmail.com> writes:
> Given that the number of sms_messages with sent_at IS NULL will remain
> pretty constant will the query continue to run at about the same speed
> or will it slow significantly as each table grows in size?
At some point the IS NULL condition will become selective enough that
it's worth using an index on sent_at to pull out just those rows.
The planner evidently thinks you are not there yet. (Or, perhaps, you
are using a version too old to be able to use an index for IS NULL?
You didn't mention your PG version, which is a fundamental mistake in
any performance-related question.)
My overall take on this is that it's folly to imagine that indexes are a
magic bullet for full-table joins: if the query has to read most/all of
the table anyway, an index doesn't help much. The plans you've shown
look perfectly sane from here.
If you were using 9.2, and the tables were relatively static, index-only
scans might make sense. But they're no magic bullet either.
regards, tom lane