Re: Should we add GUCs to allow partition pruning to be disabled?

On Wed, May 9, 2018 at 10:10 PM, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On 10 May 2018 at 14:01, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
>> I'm thinking something a bit more radical. First, since partition
>> pruning is the future and constraint exclusion is soon to be a thing of
>> the past, we should describe pruning first, and then describe exclusion
>> in terms of pruning.
>
> But... that's not true. The chapter describes inheritance partitioned
> tables too, and we're not getting rid of constraint exclusion because
> it's needed for those. However, that might not mean your patch has to
> be changed. I'd better have a look...

I agree that constraint exclusion isn't going to die any time soon,
but I think Alvaro is right to say that we should explain the new
partition pruning technique first, and then later explain, hey, we
have this constraint exclusion thing, too. It takes more work to
reorganize the documentation along those lines, but nobody wants to
read about the techniques in the order we implemented them. They want
to read about the important stuff first, and in this case, that's the
new form of partition pruning.

In defense of constraint exclusion, let me note that constraint
exclusion is not restricted to inheritance cases. It could eliminate
the need to scan a completely unpartitioned table if the WHERE clause
can be refuted by CHECK constraints. It could eliminate the need to
scan some partitions of a partitioned table based on whatever
additional CHECK constraints exist beyond the partitioning
constraints. These are less likely scenarios, perhaps, but not out of
the question. For example, imagine a partitioned order table that is
range-partitioned by order ID. You could add CHECK constraints based
on the order_date that appears in each partition, and then constraint
exclusion could eliminate partitions based on quals related to
order_date. The order date correlates with the order ID, but
partition pruning doesn't know that, so it can can only help with
quals based on order ID. Constraint exclusion doesn't have that
restriction. That's potentially useful, I think, although BRIN
indexes on each partition are another way to tackle this sort of
problem.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company