Dave E Martin <postgresql-to.dave@xxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
