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

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> 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..

Yeah, at some point (after we have an explicit notion of partitioning in
the system, instead of the current build-it-from-spare-parts approach)
we ought to look at managing this stuff at runtime rather than expecting
that exclusion can be proven at plan time.  In particular a plan type
that acted like an indexscan across the whole partitioned table (select
proper partition, then indexscan) would be real handy.
        regards, tom lane