Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 6EAEF63293E for ; Mon, 8 Mar 2010 18:39:32 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 26511-09 for ; Mon, 8 Mar 2010 22:39:16 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from outmail136082.authsmtp.co.uk (outmail136082.authsmtp.co.uk [62.13.136.82]) by mail.postgresql.org (Postfix) with ESMTP id 8B493632621 for ; Mon, 8 Mar 2010 18:39:21 -0400 (AST) Received: from mail-c193.authsmtp.com (mail-c193.authsmtp.com [62.13.128.118]) by punt5.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id o28MdIGA064740; Mon, 8 Mar 2010 22:39:18 GMT Received: from [192.168.1.94] ([193.93.202.106]) (authenticated bits=0) by mail.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id o28MdBf4005763; Mon, 8 Mar 2010 22:39:13 GMT Subject: Re: partition pruning From: Simon Riggs To: Robert Haas Cc: Anj Adu , pgsql-performance@postgresql.org In-Reply-To: <603c8f071003041440r2ec69a9dw91153f35ab2b3d53@mail.gmail.com> References: <603c8f071003041440r2ec69a9dw91153f35ab2b3d53@mail.gmail.com> Content-Type: text/plain Date: Mon, 08 Mar 2010 22:39:10 +0000 Message-Id: <1268087950.10620.49.camel@ebony> Mime-Version: 1.0 X-Mailer: Evolution 2.26.1 Content-Transfer-Encoding: 7bit X-Server-Quench: 6c2dd5ff-2b03-11df-97bb-002264978518 X-Report-Spam: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse X-AuthRoute: OCdxZQATClZOTQEd DAteCiN5VAwpPBRK HVkIKg5MJUcNSQVJ NksadRtFaA1bYFhD HGQLWlREUl97Wmp/ ag4fbAxefENNQQdq UlZIQlRNEQ1vHhxP WRwWL00AIQFGeHt3 Y0UsWnJZXQp8JkQp FksCQ3AEN2djdWEe BBZFfgQGIh5Kfh1H bFR2U3sIYG1JBC9q VzwLEgofGg96HWxN SxsRLE4TRkBDBiQh QREEEl0A X-Authentic-SMTP: 61633235383639.1014:706/Kp X-AuthFastPath: 255 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.521 tagged_above=-10 required=5 tests=AWL=-0.078, BAYES_00=-2.599, SUBJECT_FUZZY_TION=0.156 X-Spam-Level: X-Archive-Number: 201003/81 X-Sequence-Number: 37906 On Thu, 2010-03-04 at 17:40 -0500, Robert Haas wrote: > On Mon, Mar 1, 2010 at 2:29 PM, Anj Adu wrote: > > When I use intervals in my query e.g col1 between current_timestamp - > > interval '10 days' and current_timestamp...the optimizer checks ALL > > partitions whereas if I use col1 between 2 hardcoded dates..only > > the applicable partitions are scanned. > > Yep. This is one example of a more general principle: > constant-folding happens before planning, but anything more complex > has to wait until execution time. So the plan can't take into account > the value of current_timestamp in forming the plan. It could, but it doesn't yet. Partition removal can take place in the executor and this is currently targeted for 9.1. -- Simon Riggs www.2ndQuadrant.com