Re: Should we add GUCs to allow partition pruning to be disabled? - Mailing list pgsql-hackers

Hi.

On 2018/05/11 4:45, Alvaro Herrera wrote:
> I'm thinking something like this.

+1 to this more radical overhaul of this part of the documentation.

> The examples for runtime pruning are lame -- in the first, the text says
> "watch out for Subplans Removed" and then the example provided doesn't
> show one.  (That example is probably exercising the wrong thing.)
> 
> Anyway, wording suggestions for 5.10.4 and 5.10.5 in the attached file
> are welcome.

A few comments.

1. At the beginning of 5.10.4, in this example EXPLAIN's output:

SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

There used to be [1] ellipses to show discontinuation between partitions
shown in the output plan, which no longer exists.  Should be like this:

->  Seq Scan on measurement_y2006m03  (cost=0.00..33.12 rows=617 width=0)
    Filter: (logdate >= '2008-01-01'::date)
...
->  Seq Scan on measurement_y2007m11  (cost=0.00..33.12 rows=617 width=0)
    Filter: (logdate >= '2008-01-01'::date)

2. In the following sentence in 5.10.5

"Constraint exclusion works in a very similar way to partition pruning,
except that it uses each table's CHECK constraints — which gives it its
name — instead of the partitioning constraints, as with partition pruning.
Another difference is that it is only applied at plan time; there is no
attempt to remove partitions at execution time."

I think that saying "instead of the partitioning constraints, as with
partition pruning" here may be a bit misleading, because it may give
readers an impression that *all* tables have a partitioning constraint but
constraint exclusion ignores it in favor of using CHECK constraints.  How
about saying:

whereas partition pruning uses a table's partitioning constraint which
exists only in the case of declarative partitioning.

3. Do we want the following sentence 5.10.5 to be revised now?

"The default (and recommended) setting of constraint_exclusion is actually
neither on nor off, but an intermediate setting called partition, which
causes the technique to be applied only to queries that are likely to be
working on inheritance partitioned tables."

I'm not sure if it's the time yet, but maybe we would want to recommend
"on" and mention that users may want to switch to "partition" if they need
to use legacy inheritance partitioning for one reason or another.

4. In the following sentence in the caveats part of 5.10.5. Partitioning
and Constraint Exclusion

"A good rule of thumb is that partitioning constraints should contain only
comparisons of the partitioning column(s) to constants using
B-tree-indexable operators, which applies even to partitioned tables,
because only B-tree-indexable column(s) are allowed in the partition key."

The part beginning with ", which applies even to partitioned tables" is no
longer needed as I had pointed out upthread [2].  The reason is we no
longer pass the partition key derived partition constraints to constraint
exclusion algorithm, as the new pruning covers that base.

5. The last sentence in caveats, that is,

"Partitioning using these techniques will work well with up to perhaps a
hundred partitions; don't try to use many thousands of partitions."

should perhaps be reworded as:

"So the legacy inheritance based partitioning will work well with up to
perhaps a hundred partitions; don't try to use many thousands of partitions."

Thanks,
Amit

[1] https://www.postgresql.org/docs/10/static/ddl-partitioning.html

[2]
https://www.postgresql.org/message-id/a8ad3dd8-ef30-bbd0-6732-a673710378fa%40lab.ntt.co.jp