Re: queries with subquery constraints on partitioned tables not optimized?

On Tue, Feb 2, 2010 at 7:14 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Davor J." <DavorJ(at)live(dot)com> writes:
> > Now, if one takes a subquery for "1", the optimizer evaluates it first
> > (let's say to "1"), but then searches for it (sequentially) in every
> > partition, which, for large partitions, can be very time-consuming and
> goes
> > beyond the point of partitioning.
>
> No, the optimizer doesn't "evaluate it first". Subqueries aren't ever
> assumed to reduce to constants. (If you actually do have a constant
> expression, why don't you just leave out the word SELECT?)
>
> regards, tom lane

If you don't have a constant expression then you can either explicitly loop
in the calling code or a function or you could index the key in all the
subtables. The index isn't really optimal but it gets the job done.

Nik