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

Brendan Jurd wrote:
> On Thu, Nov 13, 2008 at 4:52 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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
> 
> It seems like the cure is worse than the disease here.  Surely a user
> who has a self-contradictory clause will realise the problem pretty
> quickly (i.e., when he receives zero rows) and then just fix it.
> 
> I guess my question is, what's the real benefit of going to all this
> trouble trying to prove that clauses are false? What real-world
> problem does it address?

Constraint exclusion partitioning?

Which brings to mind an interesting customer case. They are running 
queries like "WHERE id IN (...)", where ... is a *very* long list of 
keys, against a table that's partitioned by ranges of id. The query was 
running slow, because while constraint exclusion was able to eliminate 
completely useless partitions, if there was even one id in the list that 
falls into a given partition, the partition was probed for *all* of the 
ids, even those that belong to other partitions. Ideally, we would not 
only prove/refute the whole "x = ANY" expression, but individual values 
within it.

Actually, the long list of keys was obtained by running another query 
first. They originally had a single query with a join, but they split it 
to two queries because constraint exclusion doesn't work at run-time..

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com