agora inbox for [email protected]
help / color / mirror / Atom feed[PATCH 12/18] doc review: piecemeal construction of partitioned indexes
29+ messages / 5 participants
[nested] [flat]
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/21] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--jI8keyz6grp/JLjh
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 02/32] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 30e4170963..354f9e57bd 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3957,8 +3957,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--4eRLI4hEmsdu6Npr
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0003-doc-review-pg_stat_progress_create_index.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH 12/18] doc review: piecemeal construction of partitioned indexes
@ 2021-02-03 06:51 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Justin Pryzby @ 2021-02-03 06:51 UTC (permalink / raw)
5efd604ec0a3bdde98fe19d8cada69ab4ef80db3
backpatch to v11
---
doc/src/sgml/ddl.sgml | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e9a4625cc..a8cbd45d35 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3962,8 +3962,8 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
This is very
- convenient, as not only will the existing partitions become indexed, but
- also any partitions that are created in the future will. One limitation is
+ convenient, as not only the existing partitions will be indexed, but
+ so will any partitions that are created in the future. One limitation is
that it's not possible to use the <literal>CONCURRENTLY</literal>
qualifier when creating such a partitioned index. To avoid long lock
times, it is possible to use <command>CREATE INDEX ON ONLY</command>
--
2.17.0
--lc9FT7cWel8HagAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0013-doc-review-for-FDW-bulk-inserts.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
* Use of additional index columns in rows filtering
@ 2023-02-15 08:57 Maxim Ivanov <[email protected]>
2023-02-15 13:48 ` Re: Use of additional index columns in rows filtering Tomas Vondra <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: Maxim Ivanov @ 2023-02-15 08:57 UTC (permalink / raw)
To: pgsql-hackers
Hi All,
I'd like to report what seems to be a missing optimization opportunity or understand why it is not possible to achieve.
TLDR; additional index column B specified in CREATE INDEX .. (A) INCLUDE(B) is not used to filter rows in queries like WHERE B = $1 ORDER BY A during IndexScan. https://dbfiddle.uk/iehtq44L
Take following database:
CREATE TABLE t(
a integer NOT NULL,
b integer NOT NULL,
d integer NOT NULL
);
CREATE UNIQUE INDEX t_a_include_b ON t (a) INCLUDE (b);
-- I'd expect index above to behave as index below for the purpose
-- of this query
-- CREATE UNIQUE INDEX ON t(a,b);
INSERT INTO t(
SELECT random() * 100000000 as a,
random() * 3 as b,
generate_series as d FROM generate_series(1,200000)
) ON CONFLICT DO NOTHING;
If we filter on `a` and `b` columns while scanning index created as `(a) INCLUDE (b)` it seems to be fetching tuples from heap to check for condition `b = 4` despite both columns available in the index:
SELECT * FROM t WHERE a > 1000000 and b = 4 ORDER BY a ASC LIMIT 10;
Here is the plan (notice high "shared hit"):
Limit (cost=0.42..10955.01 rows=1 width=12) (actual time=84.283..84.284 rows=0 loops=1)
Output: a, b, d
Buffers: shared hit=198307
-> Index Scan using t_a_include_b on public.t (cost=0.42..10955.01 rows=1 width=12) (actual time=84.280..84.281 rows=0 loops=1)
Output: a, b, d
Index Cond: (t.a > 1000000)
Filter: (t.b = 4)
Rows Removed by Filter: 197805
Buffers: shared hit=198307
Planning:
Buffers: shared hit=30
Planning Time: 0.201 ms
Execution Time: 84.303 ms
And here is the plan with index on (a,b).
Limit (cost=0.42..4447.90 rows=1 width=12) (actual time=6.883..6.884 rows=0 loops=1)
Output: a, b, d
Buffers: shared hit=613
-> Index Scan using t_a_b_idx on public.t (cost=0.42..4447.90 rows=1 width=12) (actual time=6.880..6.881 rows=0 loops=1)
Output: a, b, d
Index Cond: ((t.a > 1000000) AND (t.b = 4))
Buffers: shared hit=613
Planning:
Buffers: shared hit=41
Planning Time: 0.314 ms
Execution Time: 6.910 ms
Because query doesn't sort on `b`, only filters on it while sorting on `a`, I'd expect indexes `(a) INCLUDE (b)` and `(a,b)` behave exactly the same with this particular query.
Interestingly, IndexOnlyScan is capable of using additional columns to filter rows without fetching them from the heap, but only for visibible tuples:
VACUUM FREEZE t;
SELECT a,b FROM t WHERE a > 1000000 and b = 4 ORDER BY a ASC LIMIT 10;
Limit (cost=0.42..6619.76 rows=1 width=8) (actual time=18.479..18.480 rows=0 loops=1)
Output: a, b
Buffers: shared hit=662
-> Index Only Scan using t_a_include_b on public.t (cost=0.42..6619.76 rows=1 width=8) (actual time=18.477..18.477 rows=0 loops=1)
Output: a, b
Index Cond: (t.a > 1000000)
Filter: (t.b = 4)
Rows Removed by Filter: 197771
Heap Fetches: 0
Buffers: shared hit=662
Removing VACUUM makes it behave like IndexScan and fetch candidate tuples from heap all while returning zero rows in the result.
To make query plan comparable I had to force index scan on both with:
SET enable_bitmapscan to off;
SET enable_seqscan to off;
SET max_parallel_workers_per_gather = 0;
Self contained fully reproducible example is in https://dbfiddle.uk/iehtq44L
Regards,
Maxim
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Use of additional index columns in rows filtering
2023-02-15 08:57 Use of additional index columns in rows filtering Maxim Ivanov <[email protected]>
@ 2023-02-15 13:48 ` Tomas Vondra <[email protected]>
2023-02-15 15:18 ` Re: Use of additional index columns in rows filtering Tom Lane <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: Tomas Vondra @ 2023-02-15 13:48 UTC (permalink / raw)
To: Maxim Ivanov <[email protected]>; pgsql-hackers
On 2/15/23 09:57, Maxim Ivanov wrote:
> Hi All,
>
> I'd like to report what seems to be a missing optimization
> opportunity or understand why it is not possible to achieve.
>
> TLDR; additional index column B specified in CREATE INDEX .. (A)
> INCLUDE(B) is not used to filter rows in queries like WHERE B = $1
> ORDER BY A during IndexScan. https://dbfiddle.uk/iehtq44L
>
> ...
>
> Here is the plan (notice high "shared hit"):
>
> Limit (cost=0.42..10955.01 rows=1 width=12) (actual time=84.283..84.284 rows=0 loops=1)
> Output: a, b, d
> Buffers: shared hit=198307
> -> Index Scan using t_a_include_b on public.t (cost=0.42..10955.01 rows=1 width=12) (actual time=84.280..84.281 rows=0 loops=1)
> Output: a, b, d
> Index Cond: (t.a > 1000000)
> Filter: (t.b = 4)
> Rows Removed by Filter: 197805
> Buffers: shared hit=198307
> Planning:
> Buffers: shared hit=30
> Planning Time: 0.201 ms
> Execution Time: 84.303 ms
>
Yeah. The reason for this behavior is pretty simple:
1) When matching clauses to indexes in match_clause_to_index(), we only
look at key columns (nkeycolumns). We'd need to check all columns
(ncolumns) and remember if the clause matched a key or included one.
2) index_getnext_slot would need to get "candidate" TIDs using
conditions on keys, and then check the clauses on included
columns.
Seems doable, unless I'm missing some fatal issue.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Use of additional index columns in rows filtering
2023-02-15 08:57 Use of additional index columns in rows filtering Maxim Ivanov <[email protected]>
2023-02-15 13:48 ` Re: Use of additional index columns in rows filtering Tomas Vondra <[email protected]>
@ 2023-02-15 15:18 ` Tom Lane <[email protected]>
2023-02-15 17:01 ` Re: Use of additional index columns in rows filtering Tomas Vondra <[email protected]>
2023-02-15 17:15 ` Re: Use of additional index columns in rows filtering Maxim Ivanov <[email protected]>
0 siblings, 2 replies; 29+ messages in thread
From: Tom Lane @ 2023-02-15 15:18 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Maxim Ivanov <[email protected]>; pgsql-hackers
Tomas Vondra <[email protected]> writes:
> On 2/15/23 09:57, Maxim Ivanov wrote:
>> TLDR; additional index column B specified in CREATE INDEX .. (A)
>> INCLUDE(B) is not used to filter rows in queries like WHERE B = $1
>> ORDER BY A during IndexScan. https://dbfiddle.uk/iehtq44L
> Seems doable, unless I'm missing some fatal issue.
Partly this is lack of round tuits, but there's another significant
issue: there very likely are index entries corresponding to dead heap
rows. Applying random user-defined quals to values found in such rows
could produce semantic anomalies; for example, divide-by-zero failures
even though you deleted all the rows having a zero in that column.
This isn't a problem for operators found in operator families, because
we trust those to not have undesirable side effects like raising
data-dependent errors. But it'd be an issue if we started to apply
quals that aren't index quals directly to index rows before doing
the heap liveness check. (And, of course, once you've fetched the
heap row there's no point in having a special path for columns
available from the index.)
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Use of additional index columns in rows filtering
2023-02-15 08:57 Use of additional index columns in rows filtering Maxim Ivanov <[email protected]>
2023-02-15 13:48 ` Re: Use of additional index columns in rows filtering Tomas Vondra <[email protected]>
2023-02-15 15:18 ` Re: Use of additional index columns in rows filtering Tom Lane <[email protected]>
@ 2023-02-15 17:01 ` Tomas Vondra <[email protected]>
1 sibling, 0 replies; 29+ messages in thread
From: Tomas Vondra @ 2023-02-15 17:01 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Maxim Ivanov <[email protected]>; pgsql-hackers
On 2/15/23 16:18, Tom Lane wrote:
> Tomas Vondra <[email protected]> writes:
>> On 2/15/23 09:57, Maxim Ivanov wrote:
>>> TLDR; additional index column B specified in CREATE INDEX .. (A)
>>> INCLUDE(B) is not used to filter rows in queries like WHERE B = $1
>>> ORDER BY A during IndexScan. https://dbfiddle.uk/iehtq44L
>
>> Seems doable, unless I'm missing some fatal issue.
>
> Partly this is lack of round tuits, but there's another significant
> issue: there very likely are index entries corresponding to dead heap
> rows. Applying random user-defined quals to values found in such rows
> could produce semantic anomalies; for example, divide-by-zero failures
> even though you deleted all the rows having a zero in that column.
>
> This isn't a problem for operators found in operator families, because
> we trust those to not have undesirable side effects like raising
> data-dependent errors. But it'd be an issue if we started to apply
> quals that aren't index quals directly to index rows before doing
> the heap liveness check. (And, of course, once you've fetched the
> heap row there's no point in having a special path for columns
> available from the index.)
Sure, but we can do the same VM check as index-only scan, right?
That would save some of the I/O to fetch the heap tuple, as long as the
page is all-visible and the filter eliminates the tuples. It makes the
costing a bit trickier, because it needs to consider both how many pages
are all-visible and selectivity of the condition.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Use of additional index columns in rows filtering
2023-02-15 08:57 Use of additional index columns in rows filtering Maxim Ivanov <[email protected]>
2023-02-15 13:48 ` Re: Use of additional index columns in rows filtering Tomas Vondra <[email protected]>
2023-02-15 15:18 ` Re: Use of additional index columns in rows filtering Tom Lane <[email protected]>
@ 2023-02-15 17:15 ` Maxim Ivanov <[email protected]>
1 sibling, 0 replies; 29+ messages in thread
From: Maxim Ivanov @ 2023-02-15 17:15 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Tomas Vondra <[email protected]>; pgsql-hackers
> This isn't a problem for operators found in operator families, because
> we trust those to not have undesirable side effects like raising
> data-dependent errors. But it'd be an issue if we started to apply
> quals that aren't index quals directly to index rows before doing
> the heap liveness check. (And, of course, once you've fetched the
> heap row there's no point in having a special path for columns
> available from the index.)
Assuming operators are pure and don't have global side effects, is it possible to ignore any error during that check? If tuple is not visible it shouldn't matter, if it is visible then error will be reported by the same routine which does filtering now (ExecQual?).
If not, then limiting this optimization to builtin ops is something I can live with :)
^ permalink raw reply [nested|flat] 29+ messages in thread
end of thread, other threads:[~2023-02-15 17:15 UTC | newest]
Thread overview: 29+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/21] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 02/32] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2021-02-03 06:51 [PATCH 12/18] doc review: piecemeal construction of partitioned indexes Justin Pryzby <[email protected]>
2023-02-15 08:57 Use of additional index columns in rows filtering Maxim Ivanov <[email protected]>
2023-02-15 13:48 ` Re: Use of additional index columns in rows filtering Tomas Vondra <[email protected]>
2023-02-15 15:18 ` Re: Use of additional index columns in rows filtering Tom Lane <[email protected]>
2023-02-15 17:01 ` Re: Use of additional index columns in rows filtering Tomas Vondra <[email protected]>
2023-02-15 17:15 ` Re: Use of additional index columns in rows filtering Maxim Ivanov <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox