BRIN is missing in multicolumn indexes documentation
Hello,
the http://www.postgresql.org/docs/9.5/static/indexes-multicolumn.html
page doesn't mention BRIN support, but according to the
http://www.postgresql.org/docs/9.5/static/sql-createindex.html it is
supported in multicolumn setup.
The attached patch (git diff against master branch) fixes the omission
and adds "Oxford comma" before "and" to unify the writing style with
the style used in the "create index" documentation.
Regards,
Petr Jediny
Attachments:
brin-multicolumn-doc-v1.patchapplication/octet-stream; name=brin-multicolumn-doc-v1.patchDownload
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 23bbec6..f3234c6 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -395,9 +395,9 @@ CREATE INDEX test2_mm_idx ON test2 (major, minor);
</para>
<para>
- Currently, only the B-tree, GiST and GIN index types support multicolumn
- indexes. Up to 32 columns can be specified. (This limit can be
- altered when building <productname>PostgreSQL</productname>; see the
+ Currently, only the B-tree, GiST, GIN, and BRIN index types support
+ multicolumn indexes. Up to 32 columns can be specified. (This limit can
+ be altered when building <productname>PostgreSQL</productname>; see the
file <filename>pg_config_manual.h</filename>.)
</para>
On Tue, Mar 22, 2016 at 12:53 AM, Petr Jediný <petr.jediny@gmail.com> wrote:
Hello,
the http://www.postgresql.org/docs/9.5/static/indexes-multicolumn.html
page doesn't mention BRIN support, but according to the
http://www.postgresql.org/docs/9.5/static/sql-createindex.html it is
supported in multicolumn setup.The attached patch (git diff against master branch) fixes the omission
and adds "Oxford comma" before "and" to unify the writing style with
the style used in the "create index" documentation.
Good catch!
In addtion to your patch, we should add the description for
the multicolumn brin index, like the following one in the doc?
---------------
A multicolumn GIN index can be used with query conditions that
involve any subset of the index's columns. Unlike B-tree or GiST,
index search effectiveness is the same regardless of which index
column(s) the query conditions use.
---------------
Regards,
--
Fujii Masao
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Good catch!
In addtion to your patch, we should add the description for
the multicolumn brin index, like the following one in the doc?---------------
A multicolumn GIN index can be used with query conditions that
involve any subset of the index's columns. Unlike B-tree or GiST,
index search effectiveness is the same regardless of which index
column(s) the query conditions use.
---------------
I guess multicolumn BRIN behaves similarly to B-tree or GiST. But I'm
no expert, so I need someone knowledgeable to confirm this. If the
following wording is OK, I will update the patch.
-----
A multicolumn BRIN index can be used with query conditions that
involve any subset of the index's columns. Conditions on additional
columns restrict the entries returned by the index, but the condition
on the first column is the most important one for determining how much
of the index needs to be scanned.
-----
Regards,
PJ
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
I guess multicolumn BRIN behaves similarly to B-tree or GiST. But I'm
no expert, so I need someone knowledgeable to confirm this. If the
following wording is OK, I will update the patch.
Multicolumn BRIN is like GIN. Every column is indexed separately.
The order of the columns doesn't matter.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Emre Hasegeli wrote:
I guess multicolumn BRIN behaves similarly to B-tree or GiST. But I'm
no expert, so I need someone knowledgeable to confirm this. If the
following wording is OK, I will update the patch.Multicolumn BRIN is like GIN. Every column is indexed separately.
The order of the columns doesn't matter.
Right. You can use one index to cover all columns; the position of the
column in the index won't matter for a query that uses one column. The
only reason to have multiple BRIN indexes on a single table is to have
a different pages_per_range.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Multicolumn BRIN is like GIN. Every column is indexed separately.
The order of the columns doesn't matter.Right. You can use one index to cover all columns; the position of the
column in the index won't matter for a query that uses one column. The
only reason to have multiple BRIN indexes on a single table is to have
a different pages_per_range.
Thanks for the information, I've attached the patch updated to v2.
PJ
Attachments:
brin-multicolumn-doc-v2.patchapplication/octet-stream; name=brin-multicolumn-doc-v2.patchDownload
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 23bbec6..d8eafa8 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -395,9 +395,9 @@ CREATE INDEX test2_mm_idx ON test2 (major, minor);
</para>
<para>
- Currently, only the B-tree, GiST and GIN index types support multicolumn
- indexes. Up to 32 columns can be specified. (This limit can be
- altered when building <productname>PostgreSQL</productname>; see the
+ Currently, only the B-tree, GiST, GIN, and BRIN index types support
+ multicolumn indexes. Up to 32 columns can be specified. (This limit can
+ be altered when building <productname>PostgreSQL</productname>; see the
file <filename>pg_config_manual.h</filename>.)
</para>
@@ -439,6 +439,15 @@ CREATE INDEX test2_mm_idx ON test2 (major, minor);
index search effectiveness is the same regardless of which index column(s)
the query conditions use.
</para>
+
+ <para>
+ A multicolumn BRIN index can be used with query conditions that
+ involve any subset of the index's columns. Like GIN and unlike B-tree or
+ GiST, index search effectiveness is the same regardless of which index
+ column(s) the query conditions use. The only reason to have multiple BRIN
+ indexes instead of one multicolumn BRIN index on a single table is to have
+ a different <literal>pages_per_range</literal> storage parameter.
+ </para>
<para>
Of course, each column must be used with operators appropriate to the index
Comments? This is my first patch to postgresql project, so comments
are very much welcomed.
PJ
On Wed, Mar 23, 2016 at 8:40 PM, Petr Jediný <petr.jediny@gmail.com> wrote:
Multicolumn BRIN is like GIN. Every column is indexed separately.
The order of the columns doesn't matter.Right. You can use one index to cover all columns; the position of the
column in the index won't matter for a query that uses one column. The
only reason to have multiple BRIN indexes on a single table is to have
a different pages_per_range.Thanks for the information, I've attached the patch updated to v2.
PJ
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Petr Jedin� wrote:
Comments? This is my first patch to postgresql project, so comments
are very much welcomed.
I think it's fine, so I pushed it. Thanks.
I hope you already read this
https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers