On Wed, 2007-10-10 at 13:30 -0500, Kevin Grittner wrote: > >>> 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? But the planner doesn't work on probability. It works on a best-guess selectivity, as known at planning time. That's why dynamic planning was invented, which we don't do yet. Don't hold your breath either. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
