Dave E Martin <postgresql-to.dave@dave.to> writes:
> tom lane wrote:
>>> Why does it think that only 159 of the 132245 rows in outages will have
>>> join partners in ipinterface? The actual results look like they all do.
>>> It might be worth looking at the pg_stats rows for the join columns to
>>> see if there's something odd about the statistics.
> Here are the pg_stats (as of today, I haven't done any analyzes or
> vacuums since the night of my first posting) for outages and ipinterface
> (I've obscured some addresses in the ipiddr row, and removed iphostname):
Oh, I see it: the most common values in the outages table have nothing
to do with the most common values in the ipinterface table. (Not
surprising ... presumably only a small part of your interfaces have
recurring problems.) The calculation that the planner does therefore
leads to the conclusion that the join will be fairly sparse.
I guess at this point I'm wondering why neither nodeid nor ipaddr is a
unique key for ipinterface ... is their combination unique?
If you could get rid of the apparently rather prevalent 0.0.0.0 entries
in ipinterface, you'd probably see a better estimation result. (I'm too
lazy to go check, but if you can replace these with nulls I think it
will deter the planner from making the bogus calculation.)
regards, tom lane