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(at)sss(dot)pgh(dot)pa(dot)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