Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 6A9701337CEC for ; Mon, 25 Apr 2011 15:11:44 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 94931-03-5 for ; Mon, 25 Apr 2011 18:11:37 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp-4.01.com (smtp-4.01.com [38.102.63.183]) by mail.postgresql.org (Postfix) with ESMTP id 169A71337BF7 for ; Mon, 25 Apr 2011 15:06:47 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp-4.01.com (Postfix) with ESMTP id 7A42643F7 for ; Mon, 25 Apr 2011 13:06:48 -0500 (CDT) Received: from smtp-4.01.com ([127.0.0.1]) by localhost (smtp-4.01.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id KGck3-plIcSH for ; Mon, 25 Apr 2011 13:06:48 -0500 (CDT) Received: by smtp-4.01.com (Postfix, from userid 99) id 53B0443F9; Mon, 25 Apr 2011 13:06:48 -0500 (CDT) Received: from Sidney-Stratton.local (70-36-143-243.dsl.dynamic.sonic.net [70.36.143.243]) by smtp-4.01.com (Postfix) with ESMTPSA id 19E8943E9 for ; Mon, 25 Apr 2011 13:06:48 -0500 (CDT) Message-ID: <4DB5B837.2010602@agliodbs.com> Date: Mon, 25 Apr 2011 11:06:47 -0700 From: Josh Berkus Organization: PostgreSQL Experts Inc. User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.2.15) Gecko/20110303 Thunderbird/3.1.9 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Issue with partition elimination References: <505432.93484.qm@web120209.mail.ne1.yahoo.com> In-Reply-To: <505432.93484.qm@web120209.mail.ne1.yahoo.com> X-Enigmail-Version: 1.1.1 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201104/342 X-Sequence-Number: 43404 On 4/21/11 6:26 PM, Paul Pierce wrote: > What was noticed, that the only case when Postgres is actually going to execute > the query against the right partitions is query #1. > > Is that by design? Second query join, will also result in 3 days(3 partitions) Partition elimination currently can only handle constants and expressions which are equivalent to constants. It will not filter on Joins successfully. This will improve somewhat in 9.1, possibly enough to fix your case. Please test this on 9.1a5 and see how well it works, and give us feedback. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com