Re: BUG #14608: no index scan with NOT IN and ENUM

On 31/03/17 at 15:49 -0400, Tom Lane wrote:
> Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
> > "lucas" == lucas <lucas(at)lucas-nussbaum(dot)net> writes:
> > lucas> I would expect postgresql to figure out that "not in ('1', '2',
> > lucas> '3', '4')" is equivalent to "d = '0'" given that the column is
> > lucas> NOT NULL. And use the index.
>
> > Well, whatever you expect, nobody has ever written code for the planner
> > to make it figure that out (and it doesn't seem likely to be a major
> > win, so probably not surprising that nobody did it).
>
> TBH, even if someone submitted a patch that did that, it would probably
> get rejected on the grounds that it consumed too many cycles in cases
> where it failed to yield a win --- which would be most of the time.
>
> Another problem is that ALTER TYPE ADD VALUE would have to invalidate
> plans depending on such a deduction. The infrastructure needed to cope
> with that would add still more distributed overhead, which would be paid
> by everybody whether or not they ever issued a query that could benefit.

OK, I see your point.

Note, though, that such reasoning is done for boolean values. For
example, "b <> false" seems to be converted to "b = true" if that makes
it possible to use an index. But I also see how simplifying boolean
expressions makes more sense than rewriting ENUM-based expressions.

Thanks

Lucas