Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitbebc469

Browse files
committed
docs: Rework sections on partition pruning/exclusion
Explain partition pruning more thoroughly, in a section above the onethat explains constraint exclusion, since the new feature is the onethat will be used more extensively from now on. Move some of thematerial from the constraint exclusion subsection to the one onpartition pruning, so that we can explain the legacy method byexplaining the differences with the new one instead of repeating it.Author: David Rowley, Álvaro HerreraReviewed-by: Amit Langote, David G. Johnston, Justin PryzbyDiscussion:https://postgr.es/m/CAKJS1f8PECxEi1YQ9nhVtshtfOMHUzAMm_Zp4gGCOCnMPjEKJA@mail.gmail.com
1 parentd9fcf7f commitbebc469

File tree

2 files changed

+163
-59
lines changed

2 files changed

+163
-59
lines changed

‎doc/src/sgml/config.sgml‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3859,6 +3859,7 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
38593859
the planner's ability to generate query plans which allow the query
38603860
executor to remove (ignore) partitions during query execution. The
38613861
default is <literal>on</literal>.
3862+
See <xref linkend="ddl-partition-pruning"/> for details.
38623863
</para>
38633864
</listitem>
38643865
</varlistentry>

‎doc/src/sgml/ddl.sgml‎

Lines changed: 162 additions & 59 deletions
Original file line numberDiff line numberDiff line change
@@ -2946,7 +2946,7 @@ VALUES ('Albany', NULL, NULL, 'NY');
29462946
divide a table into pieces called partitions. The table that is divided
29472947
is referred to as a <firstterm>partitioned table</firstterm>. The
29482948
specification consists of the <firstterm>partitioning method</firstterm>
2949-
and a list of columns or expressions to be used as the
2949+
and a list of columns or expressions to be used as the
29502950
<firstterm>partition key</firstterm>.
29512951
</para>
29522952

@@ -3759,105 +3759,211 @@ ANALYZE measurement;
37593759
</sect3>
37603760
</sect2>
37613761

3762-
<sect2 id="ddl-partitioning-constraint-exclusion">
3763-
<title>Partitioning and Constraint Exclusion</title>
3762+
<sect2 id="ddl-partition-pruning">
3763+
<title>Partition Pruning</title>
37643764

37653765
<indexterm>
3766-
<primary>constraint exclusion</primary>
3766+
<primary>partition pruning</primary>
37673767
</indexterm>
37683768

37693769
<para>
3770-
<firstterm>Constraint exclusion</firstterm> is a query optimization technique
3771-
that improves performance for partitioned tables defined in the
3772-
fashion described above (both declaratively partitioned tables and those
3773-
implemented using inheritance). As an example:
3770+
<firstterm>Partition pruning</firstterm> is a query optimization technique
3771+
that improves performance for partitioned tables. As an example:
37743772

37753773
<programlisting>
3776-
SETconstraint_exclusion = on;
3774+
SETenable_partition_pruning = on; -- the default
37773775
SELECT count(*) FROM measurement WHERE logdate &gt;= DATE '2008-01-01';
37783776
</programlisting>
37793777

3780-
Withoutconstraint exclusion, the above query would scan each of
3781-
thepartitions of the <structname>measurement</structname> table. With constraint
3782-
exclusionenabled, the planner will examine theconstraints of each
3783-
partition and try to prove that the partition need not
3778+
Withoutpartition pruning, the above query would scan each of the
3779+
partitions of the <structname>measurement</structname> table. With
3780+
partition pruningenabled, the planner will examine thedefinition
3781+
of each partition and prove that the partition need not
37843782
be scanned because it could not contain any rows meeting the query's
37853783
<literal>WHERE</literal> clause. When the planner can prove this, it
3786-
excludes the partition from the query plan.
3784+
excludes (<firstterm>prunes</firstterm>) the partition from the query
3785+
plan.
37873786
</para>
37883787

37893788
<para>
3790-
You can use the <command>EXPLAIN</command> command to show the difference
3791-
between a plan with <varname>constraint_exclusion</varname> on and a plan
3792-
with it off. A typical unoptimized plan for this type of table setup is:
3793-
3789+
You can use the <command>EXPLAIN</command> command to show the
3790+
difference between a plan whose partitions have been pruned from one
3791+
whose partitions haven't, by using the
3792+
<xref linkend="guc-enable-partition-pruning"/> configuration
3793+
parameter. A typical unoptimized plan for this type of table setup
3794+
is:
37943795
<programlisting>
3795-
SETconstraint_exclusion = off;
3796+
SETenable_partition_pruning = off;
37963797
EXPLAIN SELECT count(*) FROM measurement WHERE logdate &gt;= DATE '2008-01-01';
3797-
3798-
QUERY PLAN
3799-
-----------------------------------------------------------------------------------------------
3800-
Aggregate (cost=158.66..158.68 rows=1 width=0)
3801-
-&gt; Append (cost=0.00..151.88 rows=2715 width=0)
3802-
-&gt; Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)
3803-
Filter: (logdate &gt;= '2008-01-01'::date)
3804-
-&gt; Seq Scan on measurement_y2006m02 measurement (cost=0.00..30.38 rows=543 width=0)
3798+
QUERY PLAN
3799+
───────────────────────────────────────────────────────────────────────────────────
3800+
Aggregate (cost=188.76..188.77 rows=1 width=8)
3801+
-&gt; Append (cost=0.00..181.05 rows=3085 width=0)
3802+
-&gt; Seq Scan on measurement_y2006m02 (cost=0.00..33.12 rows=617 width=0)
38053803
Filter: (logdate &gt;= '2008-01-01'::date)
3806-
-&gt; Seq Scan on measurement_y2006m03measurement(cost=0.00..30.38 rows=543 width=0)
3804+
-&gt; Seq Scan on measurement_y2006m03(cost=0.00..33.12 rows=617 width=0)
38073805
Filter: (logdate &gt;= '2008-01-01'::date)
38083806
...
3809-
-&gt; Seq Scan onmeasurement_y2007m12 measurement(cost=0.00..30.38 rows=543 width=0)
3807+
-&gt; Seq Scan onmeasurement_y2007m11(cost=0.00..33.12 rows=617 width=0)
38103808
Filter: (logdate &gt;= '2008-01-01'::date)
3811-
-&gt; Seq Scan on measurement_y2008m01 measurement (cost=0.00..30.38 rows=543 width=0)
3809+
-&gt; Seq Scan on measurement_y2007m12 (cost=0.00..33.12 rows=617 width=0)
3810+
Filter: (logdate &gt;= '2008-01-01'::date)
3811+
-&gt; Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617 width=0)
38123812
Filter: (logdate &gt;= '2008-01-01'::date)
38133813
</programlisting>
38143814

38153815
Some or all of the partitions might use index scans instead of
38163816
full-table sequential scans, but the point here is that there
38173817
is no need to scan the older partitions at all to answer this query.
3818-
When we enableconstraint exclusion, we get a significantly
3818+
When we enablepartition pruning, we get a significantly
38193819
cheaper plan that will deliver the same answer:
3820-
38213820
<programlisting>
3822-
SETconstraint_exclusion = on;
3821+
SETenable_partition_pruning = on;
38233822
EXPLAIN SELECT count(*) FROM measurement WHERE logdate &gt;= DATE '2008-01-01';
3824-
QUERY PLAN
3825-
-----------------------------------------------------------------------------------------------
3826-
Aggregate (cost=63.47..63.48 rows=1 width=0)
3827-
-&gt; Append (cost=0.00..60.75 rows=1086 width=0)
3828-
-&gt; Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)
3829-
Filter: (logdate &gt;= '2008-01-01'::date)
3830-
-&gt; Seq Scan on measurement_y2008m01 measurement (cost=0.00..30.38 rows=543 width=0)
3823+
QUERY PLAN
3824+
───────────────────────────────────────────────────────────────────────────────────
3825+
Aggregate (cost=37.75..37.76 rows=1 width=8)
3826+
-&gt; Append (cost=0.00..36.21 rows=617 width=0)
3827+
-&gt; Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617 width=0)
38313828
Filter: (logdate &gt;= '2008-01-01'::date)
38323829
</programlisting>
38333830
</para>
38343831

38353832
<para>
3836-
Note that constraint exclusion is driven only by <literal>CHECK</literal>
3837-
constraints, not by the presence of indexes. Therefore it isn't
3838-
necessary to define indexes on the key columns. Whether an index
3839-
needs to be created for a given partition depends on whether you
3840-
expect that queries that scan the partition will generally scan
3841-
a large part of the partition or just a small part. An index will
3842-
be helpful in the latter case but not the former.
3833+
Note that partition pruning is driven only by the constraints defined
3834+
implicitly by the partition keys, not by the presence of indexes.
3835+
Therefore it isn't necessary to define indexes on the key columns.
3836+
Whether an index needs to be created for a given partition depends on
3837+
whether you expect that queries that scan the partition will
3838+
generally scan a large part of the partition or just a small part.
3839+
An index will be helpful in the latter case but not the former.
3840+
</para>
3841+
3842+
<para>
3843+
Partition pruning can be performed not only during the planning of a
3844+
given query, but also during its execution. This is useful as it can
3845+
allow more partitions to be pruned when clauses contain expressions
3846+
whose values are not known at query planning time; for example,
3847+
parameters defined in a <command>PREPARE</command> statement, using a
3848+
value obtained from a subquery or using a parameterized value on the
3849+
inner side of a nested loop join. Partition pruning during execution
3850+
can be performed at any of the following times:
3851+
3852+
<itemizedlist>
3853+
<listitem>
3854+
<para>
3855+
During initialization of the query plan. Partition pruning can be
3856+
performed here for parameter values which are known during the
3857+
initialization phase of execution. Partitions which are pruned
3858+
during this stage will not show up in the query's
3859+
<command>EXPLAIN</command> or <command>EXPLAIN ANALYZE</command>.
3860+
It is possible to determine the number of partitions which were
3861+
removed during this phase by observing the
3862+
<quote>Subplans Removed</quote> property in the
3863+
<command>EXPLAIN</command> output.
3864+
</para>
3865+
</listitem>
3866+
3867+
<listitem>
3868+
<para>
3869+
During actual execution of the query plan. Partition pruning may
3870+
also be performed here to remove partitions using values which are
3871+
only known during actual query execution. This includes values
3872+
from subqueries and values from execution-time parameters such as
3873+
those from parameterized nested loop joins. Since the value of
3874+
these parameters may change many times during the execution of the
3875+
query, partition pruning is performed whenever one of the
3876+
execution parameters being used by partition pruning changes.
3877+
Determining if partitions were pruned during this phase requires
3878+
careful inspection of the <literal>nloops</literal> property in
3879+
the <command>EXPLAIN ANALYZE</command> output.
3880+
</para>
3881+
</listitem>
3882+
</itemizedlist>
3883+
</para>
3884+
3885+
<para>
3886+
Partition pruning can be disabled using the
3887+
<xref linkend="guc-enable-partition-pruning"/> setting.
3888+
</para>
3889+
3890+
<note>
3891+
<para>
3892+
Currently, pruning of partitions during the planning of an
3893+
<command>UPDATE</command> or <command>DELETE</command> command is
3894+
implemented using the constraint exclusion method (however, it is
3895+
still ruled by the <literal>enable_partition_pruning</literal>
3896+
setting instead of <literal>constraint_exclusion</literal>) &mdash;
3897+
see the next section for details and caveats that apply.
3898+
</para>
3899+
3900+
<para>
3901+
Also, execution-time partition pruning currently only occurs for the
3902+
<literal>Append</literal> node type, not <literal>MergeAppend</literal>.
3903+
</para>
3904+
3905+
<para>
3906+
Both of these behaviors are likely to be changed in a future release
3907+
of <productname>PostgreSQL</productname>.
3908+
</para>
3909+
</note>
3910+
</sect2>
3911+
3912+
<sect2 id="ddl-partitioning-constraint-exclusion">
3913+
<title>Partitioning and Constraint Exclusion</title>
3914+
3915+
<indexterm>
3916+
<primary>constraint exclusion</primary>
3917+
</indexterm>
3918+
3919+
<para>
3920+
<firstterm>Constraint exclusion</firstterm> is a query optimization
3921+
technique similar to partition pruning. While it is primarily used
3922+
for partitioned tables using the legacy inheritance method, it can be
3923+
used for other purposes, including with declarative partitioning.
3924+
</para>
3925+
3926+
<para>
3927+
Constraint exclusion works in a very similar way to partition
3928+
pruning, except that it uses each table's <literal>CHECK</literal>
3929+
constraints &mdash; which gives it its name &mdash; whereas partition
3930+
pruning uses the table's partitioning constraint, which exists only in
3931+
the case of declarative partitioning. Another difference is that it
3932+
is only applied at plan time; there is no attempt to remove
3933+
partitions at execution time.
3934+
</para>
3935+
3936+
<para>
3937+
The fact that constraint exclusion uses <literal>CHECK</literal>
3938+
constraints, which makes it slow compared to partition pruning, can
3939+
sometimes be used as an advantage: because constraints can be defined
3940+
even on declaratively-partitioned tables, in addition to the internal
3941+
partitioning constraints, and only constraint exclusion would be able
3942+
to elide certain partitions from the query plan using those.
38433943
</para>
38443944

38453945
<para>
38463946
The default (and recommended) setting of
3847-
<xref linkend="guc-constraint-exclusion"/> isactuallyneither
3947+
<xref linkend="guc-constraint-exclusion"/> is neither
38483948
<literal>on</literal> nor <literal>off</literal>, but an intermediate setting
38493949
called <literal>partition</literal>, which causes the technique to be
3850-
applied only to queries that are likely to be working on partitioned
3950+
applied only to queries that are likely to be working oninheritancepartitioned
38513951
tables. The <literal>on</literal> setting causes the planner to examine
38523952
<literal>CHECK</literal> constraints in all queries, even simple ones that
38533953
are unlikely to benefit.
38543954
</para>
38553955

38563956
<para>
3857-
The following caveats apply to constraint exclusion, which is used by
3858-
both inheritance and partitioned tables:
3957+
The following caveats apply to constraint exclusion:
38593958

38603959
<itemizedlist>
3960+
<listitem>
3961+
<para>
3962+
Constraint exclusion is only applied during query planning; it is
3963+
not applied at execution time like partition pruning does.
3964+
</para>
3965+
</listitem>
3966+
38613967
<listitem>
38623968
<para>
38633969
Constraint exclusion only works when the query's <literal>WHERE</literal>
@@ -3877,21 +3983,18 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate &gt;= DATE '2008-01-01';
38773983
range tests for range partitioning, as illustrated in the preceding
38783984
examples. A good rule of thumb is that partitioning constraints should
38793985
contain only comparisons of the partitioning column(s) to constants
3880-
using B-tree-indexable operators, which applies even to partitioned
3881-
tables, because only B-tree-indexable column(s) are allowed in the
3882-
partition key. (This is not a problem when using declarative
3883-
partitioning, since the automatically generated constraints are simple
3884-
enough to be understood by the planner.)
3986+
using B-tree-indexable operators, because only B-tree-indexable
3987+
column(s) are allowed in the partition key.
38853988
</para>
38863989
</listitem>
38873990

38883991
<listitem>
38893992
<para>
38903993
All constraints on all partitions of the master table are examined
38913994
during constraint exclusion, so large numbers of partitions are likely
3892-
to increase query planning time considerably.Partitioning using
3893-
these techniqueswill work well with up to perhaps a hundred partitions;
3894-
don't try to use many thousands of partitions.
3995+
to increase query planning time considerably.So the legacy
3996+
inheritance based partitioningwill work well with up to perhaps a
3997+
hundred partitions;don't try to use many thousands of partitions.
38953998
</para>
38963999
</listitem>
38974000

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp