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

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)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