>>> On Wed, Oct 10, 2007 at 1:07 PM, in message <20980.1192039650@xxxxxxxxxxxxx>, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Simon Riggs <simon@xxxxxxxxxxxxxxx> 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. Well, this query is run tens of thousands of times per day by our web application; less than one percent of those runs would require the subplan. (In my initial post I showed counts to demonstrate that 1% of the rows had a non-NULL value and, while I wouldn't expect the planner to know this, these tend to be clustered on a lower percentage of cases.) If the philosophy of the planner is to go for the lowest average cost (versus lowest worst case cost) shouldn't it use the statistics for to look at the percentage of NULLs? -Kevin ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
