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

Sergey Konoplev wrote:
>>> What configure options did you use, what locale/encoding are you using,
>>> what nondefault settings have you got in postgresql.conf?
>>>
>>> regards, tom lane
>> 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).
>
> On more thing:
>
> If you do
>
> EXPLAIN SELECT 1 FROM table1 WHERE table1_id NOT IN (SELECT column1
> FROM (VALUES (123),(456),(789),... a lot of IDs here...)_);
>
> it works as fast as with constraint_exclusion turned to off.

Good detective work sir! I can reproduce the problem here with
constraint_exclusion = on.

Presumably what it's doing is looking to see if the target table has any
relevant CHECK constraints for each of the 2000 values provided. It
won't do so for the second example because it's not smart enough to look
into the results of another select clause.

Hmm - a little bit of grepping...
backend/optimizer/util/plancat.c
566:relation_excluded_by_constraints(PlannerInfo *root
called from
backend/optimizer/path/allpaths.c

You could presumably cache the results of the exclusion test, but that's
only going to be relevant where you have the same value more than once.
You could try to be smarter and evaluate all values in one go I suppose,
or limit how many you'll test against. I'm over my head here though -
you'll have to see what Tom says.

The good news is that you can just issue "SET constraint_exclusion"
before individual queries as a temporary workaround.

--
Richard Huxton
Archonet Ltd