Hi David.
On 2018/04/09 12:48, David Rowley wrote:
> While looking at the docs in [1], I saw that we still mention:
>
> 4. Ensure that the constraint_exclusion configuration parameter is not
> disabled in postgresql.conf. If it is, queries will not be optimized
> as desired.
>
> This is no longer true. The attached patch removed it.
>
> [1] https://www.postgresql.org/docs/10/static/ddl-partitioning.htm
Thanks. I was aware of the changes that would need to be made, but you
beat me to writing the patch itself.
About the patch:
While the users no longer need to enable constraint_exclusion true for
select queries, one would still need it for update/delete queries, because
the new pruning logic only gets invoked for the former. Alas...
Also, further down on that page, there is a 5.10.4 Partitioning and
Constraint Exclusion sub-section. I think it would also need some tweaks
due to new developments.
I updated your patch to fix that. Please take a look.
Thanks,
Amit
From a4fe924936fe623ff95e6aa050b8fd7d22dbbb84 Mon Sep 17 00:00:00 2001
From: "dgrow...@gmail.com" <dgrow...@gmail.com>
Date: Mon, 9 Apr 2018 15:43:32 +1200
Subject: [PATCH v2] Remove mention of constraint_exclusion in partitioning
docs
As of 9fdb675fc5d2, this GUC now no longer has an affect on partition pruning.
---
doc/src/sgml/ddl.sgml | 25 ++++++++++++++-----------
1 file changed, 14 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index feb2ab7792..eed8753e24 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3194,13 +3194,14 @@ CREATE INDEX ON measurement (logdate);
</para>
</listitem>
- <listitem>
- <para>
- Ensure that the <xref linkend="guc-constraint-exclusion"/>
- configuration parameter is not disabled in
<filename>postgresql.conf</filename>.
- If it is, queries will not be optimized as desired.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Ensure that the <xref linkend="guc-constraint-exclusion"/>
+ configuration parameter is not disabled in
<filename>postgresql.conf</filename>.
+ While enabling it is not required for select queries, not doing so will
result
+ in update and delete queries to not be optimized as desired.
+ </para>
+ </listitem>
</orderedlist>
</para>
@@ -3767,10 +3768,12 @@ ANALYZE measurement;
</indexterm>
<para>
- <firstterm>Constraint exclusion</firstterm> is a query optimization
technique
- that improves performance for partitioned tables defined in the
- fashion described above (both declaratively partitioned tables and those
- implemented using inheritance). As an example:
+ <firstterm>Constraint exclusion</firstterm> is a query optimization
+ technique that improves performance for partitioned tables defined in the
+ fashion described above. While it is used only for update and delete
+ queries in the case of declaratively partitioned tables, it is used for all
+ queries in the case of table partitioning implemented using inheritance.
+ As an example:
<programlisting>
SET constraint_exclusion = on;
--
2.11.0
