Re: enable_sort optimization problem

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