|From:||Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>|
|To:||Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>|
|Cc:||David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, Imai Yoshikazu <yoshikazu_i443(at)live(dot)jp>, "jesper(dot)pedersen(at)redhat(dot)com" <jesper(dot)pedersen(at)redhat(dot)com>, "Imai, Yoshikazu" <imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com>, Amit Langote <amitlangote09(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Justin Pryzby <pryzby(at)telsasoft(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>|
|Subject:||Re: speeding up planning with partitions|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> writes:
> On 2019/04/02 2:34, Tom Lane wrote:
>> I'm not at all clear on what we think the interaction between
>> enable_partition_pruning and constraint_exclusion ought to be,
>> so I'm not sure what the appropriate resolution is here. Thoughts?
> Prior to 428b260f87 (that is, in PG 11), partition pruning for UPDATE and
> DELETE queries is realized by applying constraint exclusion to the
> partition constraint of the target partition. The conclusion of the
> discussion when adding the enable_partition_pruning GUC  was that
> whether or not constraint exclusion is carried out (to facilitate
> partition pruning) should be governed by the new GUC, not
> constraint_exclusion, if only to avoid confusing users.
I got back to thinking about how this ought to work. It appears to me
that we've got half a dozen different behaviors that depend on one or both
of these settings:
1. Use of ordinary table constraints (CHECK, NOT NULL) in baserel pruning,
that is relation_excluded_by_constraints for baserels.
This is enabled by constraint_exclusion = on.
2. Use of partition constraints in baserel pruning (applicable only
when a partition is accessed directly).
This is currently partly broken, and it's what your patch wants to
3. Use of ordinary table constraints in appendrel pruning,
that is relation_excluded_by_constraints for appendrel members.
This is enabled by constraint_exclusion >= partition.
4. Use of partition constraints in appendrel pruning.
This is enabled by the combination of enable_partition_pruning AND
constraint_exclusion >= partition. However, it looks to me like this
is now nearly if not completely useless because of #5.
5. Use of partition constraints in expand_partitioned_rtentry.
Enabled by enable_partition_pruning (see prune_append_rel_partitions).
6. Use of partition constraints in run-time partition pruning.
This is also enabled by enable_partition_pruning, cf
Now in addition to what I mention above, there are assorted random
differences in behavior depending on whether we are in an inherited
UPDATE/DELETE or not. I consider these differences to be so bogus
that I'm not even going to include them in this taxonomy; they should
not exist. The UPDATE/DELETE target ought to act the same as a baserel.
I think this is ridiculously overcomplicated even without said random
differences. I propose that we do the following:
* Get rid of point 4 by not considering partition constraints for
appendrel members in relation_excluded_by_constraints. It's just
useless cycles in view of point 5, or nearly so. (Possibly there
are corner cases where we could prove contradictions between a
relation's partition constraints and regular constraints ... but is
it really worth spending planner cycles to look for that?)
* Make point 2 like point 1 by treating partition constraints for
baserels like ordinary table constraints, ie, they are considered
only when constraint_exclusion = on (independently of whether
enable_partition_pruning is on).
* Treat an inherited UPDATE/DELETE target table as if it were an
appendrel member for the purposes of relation_excluded_by_constraints,
thus removing the behavioral differences between SELECT and UPDATE/DELETE.
With this, constraint_exclusion would act pretty much as it traditionally
has, and in most cases would not have any special impact on partitions
compared to old-style inheritance. The behaviors that
enable_partition_pruning would control are expand_partitioned_rtentry
pruning and run-time pruning, neither of which have any applicability to
regards, tom lane
|Next Message||Melanie Plageman||2019-04-22 22:12:21||Calling PrepareTempTablespaces in BufFileCreateTemp|
|Previous Message||Juan José Santamaría Flecha||2019-04-22 21:41:57||Re: TM format can mix encodings in to_char()|