From: Dimitri Fontaine <dfontaine@hi-media.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "Davor J." <DavorJ@live.com>,  pgsql-performance@postgresql.org
Subject: Re: queries with subquery constraints on partitioned tables not
	optimized?
Organization: Hi-Media
References: <hk8sd1$2b79$1@news.hub.org> <5002.1265156054@sss.pgh.pa.us>
Date: Wed, 03 Feb 2010 11:23:58 +0100
In-Reply-To: <5002.1265156054@sss.pgh.pa.us> (Tom Lane's message of "Tue, 02
	Feb 2010 19:14:14 -0500")
Message-ID: <87k4uuty69.fsf@hi-media-techno.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1.50 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Tom Lane <tgl@sss.pgh.pa.us> writes:
> "Davor J." <DavorJ@live.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?)

It's easy to experience the same problem with a JOIN you'd want to
happen at the partition level that the planner will apply on the Append
Node.

I'm yet to figure out if 8.4 is smarter about this, meanwhile I'm using
array tricks to force the push-down.

 WHERE ... 
   AND service = ANY ((SELECT array_accum(id) FROM services WHERE x=281)
                   || (SELECT array_accum(id) FROM services WHERE y=281))

It happens that I need the array concatenation more than the = ANY
operator (as compared to IN), so I also have queries using = ANY
('{}':int[] || (SELECT array_accum(x) ...))  to really force the planner
into doing the join in the partitions rather than after the Append has
taken place.

Regards,
-- 
dim

PS: If you're interrested into complete examples, I'll be able to
provide for them in private.