From: Dimitri Fontaine <dfontaine@hi-media.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Davor J. <DavorJ@live.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: queries with subquery constraints on partitioned tables not optimized?
Date: Wed, 03 Feb 2010 11:23:58 +0100
Message-ID: <87k4uuty69.fsf@hi-media-techno.com> (raw)
In-Reply-To: <5002.1265156054@sss.pgh.pa.us>
References: <hk8sd1$2b79$1@news.hub.org>
<5002.1265156054@sss.pgh.pa.us>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dfontaine@hi-media.com, tgl@sss.pgh.pa.us, DavorJ@live.com
Subject: Re: queries with subquery constraints on partitioned tables not optimized?
In-Reply-To: <87k4uuty69.fsf@hi-media-techno.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox