Date: Sun, 28 Oct 2018 16:49:24 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Dave E Martin <postgresql2-to.dave@dave.to>
Cc: pgsql-performance@postgresql.org
Subject: Re: DELETE / UPDATE from partition not optimized (11.0)
Message-ID: <20181028214924.GA26843@telsasoft.com>
References: 
 <CAFpvjbE3bETEC476aWVbEYu+eyQj5S5Mjo0+F673ck_Jrp6WHA@mail.gmail.com>
 <20181026154540.GA27004@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20181026154540.GA27004@telsasoft.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Fri, Oct 26, 2018 at 10:45:40AM -0500, Justin Pryzby wrote:
> On Thu, Oct 25, 2018 at 10:43:10AM -0600, Dave E Martin wrote:
> > If SELECT is confident enough to limit itself to one partition, why isn't
> > DELETE (or UPDATE)?
> 
> Because of this limitation:
> 
> https://www.postgresql.org/docs/current/static/ddl-partitioning.html#DDL-PARTITION-PRUNING
> |Currently, pruning of partitions during the planning of an UPDATE or DELETE
> |command is implemented using the constraint exclusion method (however, it is
> |controlled by the enable_partition_pruning rather than constraint_exclusion) —
> |see the following section for details and caveats that apply.

I meant to add that one can use a redundant constraints in addition to the
partition bounds, both specifying the same condition.  That also allows
detaching and re-attaching the partition without a table scan (which is why we
do it).

Justin