Simon Riggs <simon@2ndquadrant.com> writes: > Basically the planner doesn't ever optimise for the possibility of the > never-executed case because even a single row returned would destroy > that assumption. It's worse than that: the outer subplan *does* return some rows. I suppose that all of them had NULLs in the join keys, which means that (since 8.1 or so) nodeMergejoin discards them as unmatchable. Had even one been non-NULL the expensive subplan would have been run. This seems like too much of a corner case to justify adding a lot of machinery for. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > The point I'm trying to make is that at planning time the > pg_statistic row for this "Charge"."reopHistSeqNo" column showed > stanullfrac as 0.989; it doesn't seem to have taken this into account > when making its guess about how many rows would be joined when it was > compared to the primary key column of the "CaseHist" table. It certainly does take nulls into account, but the estimate of resulting rows was still nonzero; and even if it were zero, I'd be very hesitant to make it choose a plan that is fast only if there were exactly zero such rows and is slow otherwise. Most of the complaints we've had about issues of this sort involve the opposite problem, ie, the planner is choosing a plan that works well for few rows but falls down because reality involves many rows. "Fast-for-few-rows" plans are usually a lot more brittle than the alternatives in terms of the penalty you pay for too many rows, and so putting a thumb on the scales to push it towards a "fast" corner case sounds pretty unsafe to me. As Simon notes, the only technically sound way to handle this would involve run-time plan changeover, which is something we're not nearly ready to tackle. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > I'm not sure why it looks at the slow option at all; it seems like a remain= > ing weakness in the OUTER JOIN optimizations. I think that comes mostly from the fact that you've got non-nullable targetlist entries in the definition of the CaseTypeHistEvent view. Those prevent that view from being flattened into the upper query when it's underneath an outer join, because the current variable-evaluation rules provide no other way to ensure that the values are forced NULL when they need to be. This is something we should fix someday but don't hold your breath waiting --- it's likely to take some pretty fundamental rejiggering of the planner's handling of Vars. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings