> Hang on -- upthread the context was inner join, and the gripe was join
> fast with '=', slow with INDF. When he said the nulls were > 'generated', I didn't follow that they were part of the original > query. If the nulls are generated along with the query, sure, an > index won't help. > > I maintain my earlier point; with respect to the original query, to > get from performance of INDF to =, you have three options: > a) expr index the nulls (assuming they are physically stored) > b) convert to ((a = b) or a is null and b is null) which can help with > a bitmap or plan > c) covert to union all equivalent of "b" > > merlin a) and b) would be workaround that would run an order of magnitude slower. The query starts with a full table scan of a large table. If the planner had started elsewhere it could have reduced the result to 1-2 rows from the start. It won't choose this plan without the help from =. c) could be a acceptable workaround, but it would clutter up if you would want more than one column to be IS NOT DISTINCT FROM. You end up with 2^n unions to simulate IS NOT DISTINCT FROM. Without knowing the work required, I will still argue that having IS NOT DISTINCT FROM use the same transitive rules as equality, would be a better approach. With fear of talking about things I know little(nothing) of, I think the description of EquivalenceClasses in postgres/src/backend/optimizer/README, should be extended to also include EquivalenceClasses of IS NOT DISTINCT FROM.
