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

Tom Lane wrote:
> "Sergey Konoplev" <gray(dot)ru(at)gmail(dot)com> writes:
>> You are right. I've found the odd thing (that completely drives me
>> mad) in postgresql.conf.
>
>> You are able to reproduce slow-not-in queries by switching
>> constraint_exclusion to on in your postgresql.conf and running my test
>> (which is attached to the first message).
>
> 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?

> We could respond to this in a number of ways:
>
> 1. "Tough, don't do that."
>
> 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.

> 3. Put in a narrow hack that will get us out of this specific case,
> but might still allow very slow proof attempts in other large cases.
>
> The specific narrow hack I'm considering for #3 goes like this:

The specific hack goes right over my head :-)

--
Richard Huxton
Archonet Ltd