Re: [GENERAL] Very slow queries w/ NOT IN preparation (seems like a bug, test case)

Richard Huxton <dev(at)archonet(dot)com> writes:
> Tom Lane wrote:
>> Hmph. It's trying to see if the NOT IN condition is self-contradictory,
>> which of course it isn't, but the predicate_refuted_by machinery isn't
>> smart enough to determine that except by running through all N^2
>> combinations of the individual x <> const conditions :-(.

> So it's not checking the table, it's looking to see whether <clause1> OR
> <clause2> end up excluding each other? Presumably becuase "OR" is just
> another operator?

Yeah. An example of a closely related expression that it *would* be
able to prove self-contradictory is
WHERE x = ALL (ARRAY[1, 2, ...])
or perhaps slightly more realistically
WHERE x = ANY (ARRAY[1, 2, 3]) AND x > 4

The NOT IN is equivalent to
WHERE x <> ALL (ARRAY[1, 2, ...])
which can't be proved false. (Well, it could if x is of a finite domain
and all the possible values are listed, but we aren't gonna check for
that.) So you can see that some fairly close analysis is needed to
determine whether anything can be done or not.

>> 2. Put some arbitrary limit on the number of subconditions in an AND or
>> OR clause before we give up and don't attempt to prove anything about
>> it.

> Do we know the estimated cost of just executing the planner-node at this
> point? You could scale with the cost of actually doing the tests.

No, this is long before we've developed any cost estimates.

regards, tom lane