Re: b1 OR b2 <-> ( CASE WHEN b1 THE true ELSE b2 END ): performance bottleneck on logical OR

=?iso-8859-1?Q?C=E9dric_Dufour?= <cedric(dot)dufour(at)freesurf(dot)ch> writes:
> It appears that in the ( CASE WHEN ... THEN true ELSE ... END ) case, the
> planner uses a 'hash' join that is achieved much quicker
> than the related 'nest loop' it chooses in the ( ... OR ... ) case.

I'd like to see more details. What is evidently happening is that
the planner takes your given condition, which is an OR/AND of conditions
on Session, Owner, and both, and rewrites it into a CNF (AND/OR) form in
which some of the conjuncts are on only Session or only Owner. These
conjuncts can then be pushed down to the individual table scans rather
than applied at the level of the join.

Now as far as I can see, this pushing-down is a good thing and should
always happen. The difficulty seems to be that the planner
mis-estimates the selectivity of the pushed-down condition and deduces
a too-small output row count, causing a change in a higher plan level
from hash join to nestloop. Unfortunately you didn't show the whole
plan, and it looks like the error is in the part you didn't show.

7.3 development sources would be more useful for investigating this
than prior releases, since the current EXPLAIN code shows the conditions
being tested at each plan node, not only the node type. Would you be
interested in trying your example on a development system, or sending me
enough data to let me reproduce the example here?

regards, tom lane