pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedCREATE INDEX...USING
6+ messages / 2 participants
[nested] [flat]
* CREATE INDEX...USING
@ 2022-08-17 16:41 Jeff Janes <jeff.janes@gmail.com>
2022-08-17 18:58 ` Re: CREATE INDEX...USING Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 6+ messages in thread
From: Jeff Janes @ 2022-08-17 16:41 UTC (permalink / raw)
To: pgsql-docs; Bruce Momjian <bruce@momjian.us>
This was recently added to CREATE INDEX reference page:
+ The optional <literal>USING</literal> clause specifies an index
+ type as described in <xref linkend="indexes-types"/>. If not
+ specified, a default index type will be used based on the
+ data types of the columns.
But I think this is wrong, the default type is BTREE, it does not depend on
the data type. Or at least, I've never witnessed the claimed behavior.
The claim also conflicts with what is said at
https://www.postgresql.org/docs/10/indexes-types.html
Cheers,
Jeff
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CREATE INDEX...USING
2022-08-17 16:41 CREATE INDEX...USING Jeff Janes <jeff.janes@gmail.com>
@ 2022-08-17 18:58 ` Bruce Momjian <bruce@momjian.us>
2022-08-17 21:11 ` Re: CREATE INDEX...USING Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Bruce Momjian @ 2022-08-17 18:58 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-docs
On Wed, Aug 17, 2022 at 12:41:03PM -0400, Jeff Janes wrote:
> This was recently added to CREATE INDEX reference page:
>
> + The optional <literal>USING</literal> clause specifies an index
> + type as described in <xref linkend="indexes-types"/>. If not
> + specified, a default index type will be used based on the
> + data types of the columns.
>
> But I think this is wrong, the default type is BTREE, it does not depend on the
> data type. Or at least, I've never witnessed the claimed behavior. The claim
> also conflicts with what is said at https://www.postgresql.org/docs/10/
> indexes-types.html
You are correct --- parser/gram.y has:
access_method_clause:
USING name { $$ = $2; }
| /*EMPTY*/ { $$ = DEFAULT_INDEX_TYPE; }
and from include/catalog/index.h:
#define DEFAULT_INDEX_TYPE "btree"
Patch attached.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
Attachments:
[text/x-diff] using.diff (754B, ../../Yv06UMdbAdOMySoF@momjian.us/2-using.diff)
download | inline diff:
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 944fdb4b09..cb4b43f637 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -154,9 +154,8 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
<listitem>
<para>
The optional <literal>USING</literal> clause specifies an index
- type as described in <xref linkend="indexes-types"/>. If not
- specified, a default index type will be used based on the
- data types of the columns.
+ type as described in <xref linkend="indexes-types"/>. The
+ default is <literal>btree</literal>.
</para>
</listitem>
</varlistentry>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CREATE INDEX...USING
2022-08-17 16:41 CREATE INDEX...USING Jeff Janes <jeff.janes@gmail.com>
2022-08-17 18:58 ` Re: CREATE INDEX...USING Bruce Momjian <bruce@momjian.us>
@ 2022-08-17 21:11 ` Jeff Janes <jeff.janes@gmail.com>
2022-08-18 03:21 ` Re: CREATE INDEX...USING Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 6+ messages in thread
From: Jeff Janes @ 2022-08-17 21:11 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs
On Wed, Aug 17, 2022 at 2:58 PM Bruce Momjian <bruce@momjian.us> wrote:
> On Wed, Aug 17, 2022 at 12:41:03PM -0400, Jeff Janes wrote:
> > This was recently added to CREATE INDEX reference page:
> >
> > + The optional <literal>USING</literal> clause specifies an index
> > + type as described in <xref linkend="indexes-types"/>. If not
> > + specified, a default index type will be used based on the
> > + data types of the columns.
> >
> > But I think this is wrong, the default type is BTREE, it does not depend
> on the
> > data type. Or at least, I've never witnessed the claimed behavior. The
> claim
> > also conflicts with what is said at https://www.postgresql.org/docs/10/
> > indexes-types.html
>
> You are correct --- parser/gram.y has:
>
> access_method_clause:
> USING name { $$ = $2; }
> | /*EMPTY*/ { $$ =
> DEFAULT_INDEX_TYPE; }
>
> and from include/catalog/index.h:
>
> #define DEFAULT_INDEX_TYPE "btree"
>
> Patch attached.
>
>
>
That looks good to me. But now looking over the linked pages more, it
seems like https://www.postgresql.org/docs/current/indexes-types.html was
never changed to admit the possibility of custom index access methods (like
bloom) and neither was the 'replaceable class="parameter">method' section
of https://www.postgresql.org/docs/current/sql-createindex.html.
Also, is it odd that we say essentially the same thing for literal USING as
we say for the replaceable /method/?
Cheers,
Jeff
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CREATE INDEX...USING
2022-08-17 16:41 CREATE INDEX...USING Jeff Janes <jeff.janes@gmail.com>
2022-08-17 18:58 ` Re: CREATE INDEX...USING Bruce Momjian <bruce@momjian.us>
2022-08-17 21:11 ` Re: CREATE INDEX...USING Jeff Janes <jeff.janes@gmail.com>
@ 2022-08-18 03:21 ` Bruce Momjian <bruce@momjian.us>
2022-08-18 16:32 ` Re: CREATE INDEX...USING Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 6+ messages in thread
From: Bruce Momjian @ 2022-08-18 03:21 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-docs
On Wed, Aug 17, 2022 at 05:11:23PM -0400, Jeff Janes wrote:
> That looks good to me. But now looking over the linked pages more, it seems
> like https://www.postgresql.org/docs/current/indexes-types.html was never
> changed to admit the possibility of custom index access methods (like bloom)
Uh, bloom is in /contrib, so we wouldn't mention that in the main docs,
I think. However, it might be nice to mention you can add others.
> and neither was the 'replaceable class="parameter">method' section of https://
> www.postgresql.org/docs/current/sql-createindex.html.
Yes, seems we should say that you can install your own methods that can
be used, e.g., bloom.
> Also, is it odd that we say essentially the same thing for literal USING as we
> say for the replaceable /method/?
Well, this is embarrassing. Someone reported there was no mention of
USING in the CREATE INDEX docs, and I didn't see it either, so I added
it.
However, CREATE INDEX just lists the parameters, not the keywords, so it
was already there as 'method', as you mentioned above. I will just
remove the USING section I recently added.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CREATE INDEX...USING
2022-08-17 16:41 CREATE INDEX...USING Jeff Janes <jeff.janes@gmail.com>
2022-08-17 18:58 ` Re: CREATE INDEX...USING Bruce Momjian <bruce@momjian.us>
2022-08-17 21:11 ` Re: CREATE INDEX...USING Jeff Janes <jeff.janes@gmail.com>
2022-08-18 03:21 ` Re: CREATE INDEX...USING Bruce Momjian <bruce@momjian.us>
@ 2022-08-18 16:32 ` Bruce Momjian <bruce@momjian.us>
2022-09-01 02:39 ` Re: CREATE INDEX...USING Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 6+ messages in thread
From: Bruce Momjian @ 2022-08-18 16:32 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-docs
On Wed, Aug 17, 2022 at 11:21:58PM -0400, Bruce Momjian wrote:
> On Wed, Aug 17, 2022 at 05:11:23PM -0400, Jeff Janes wrote:
> > That looks good to me. But now looking over the linked pages more, it seems
> > like https://www.postgresql.org/docs/current/indexes-types.html was never
> > changed to admit the possibility of custom index access methods (like bloom)
>
> Uh, bloom is in /contrib, so we wouldn't mention that in the main docs,
> I think. However, it might be nice to mention you can add others.
>
> > and neither was the 'replaceable class="parameter">method' section of https://
> > www.postgresql.org/docs/current/sql-createindex.html.
>
> Yes, seems we should say that you can install your own methods that can
> be used, e.g., bloom.
>
> > Also, is it odd that we say essentially the same thing for literal USING as we
> > say for the replaceable /method/?
>
> Well, this is embarrassing. Someone reported there was no mention of
> USING in the CREATE INDEX docs, and I didn't see it either, so I added
> it.
>
> However, CREATE INDEX just lists the parameters, not the keywords, so it
> was already there as 'method', as you mentioned above. I will just
> remove the USING section I recently added.
I wrote the attached patch to address the issues above.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
Attachments:
[text/x-diff] using.diff (2.1K, ../../Yv5pnB2RRN1lIYRi@momjian.us/2-using.diff)
download | inline diff:
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 2a70e02f7c..0c3fcfd62f 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -117,7 +117,8 @@ CREATE INDEX test1_id_index ON test1 (id);
<para>
<productname>PostgreSQL</productname> provides several index types:
- B-tree, Hash, GiST, SP-GiST, GIN and BRIN.
+ B-tree, Hash, GiST, SP-GiST, GIN, BRIN, and the extension <link
+ linkend="bloom">bloom</link>.
Each index type uses a different
algorithm that is best suited to different types of queries.
By default, the <link linkend="sql-createindex"><command>CREATE
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 944fdb4b09..40986aa502 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -149,18 +149,6 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
</listitem>
</varlistentry>
- <varlistentry>
- <term><literal>USING</literal></term>
- <listitem>
- <para>
- The optional <literal>USING</literal> clause specifies an index
- type as described in <xref linkend="indexes-types"/>. If not
- specified, a default index type will be used based on the
- data types of the columns.
- </para>
- </listitem>
- </varlistentry>
-
<varlistentry>
<term><literal>INCLUDE</literal></term>
<listitem>
@@ -250,8 +238,9 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
<para>
The name of the index method to be used. Choices are
<literal>btree</literal>, <literal>hash</literal>,
- <literal>gist</literal>, <literal>spgist</literal>, <literal>gin</literal>, and
- <literal>brin</literal>.
+ <literal>gist</literal>, <literal>spgist</literal>, <literal>gin</literal>,
+ <literal>brin</literal>, or user-installed access methods like
+ <link linkend="bloom">bloom</link>.
The default method is <literal>btree</literal>.
</para>
</listitem>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CREATE INDEX...USING
2022-08-17 16:41 CREATE INDEX...USING Jeff Janes <jeff.janes@gmail.com>
2022-08-17 18:58 ` Re: CREATE INDEX...USING Bruce Momjian <bruce@momjian.us>
2022-08-17 21:11 ` Re: CREATE INDEX...USING Jeff Janes <jeff.janes@gmail.com>
2022-08-18 03:21 ` Re: CREATE INDEX...USING Bruce Momjian <bruce@momjian.us>
2022-08-18 16:32 ` Re: CREATE INDEX...USING Bruce Momjian <bruce@momjian.us>
@ 2022-09-01 02:39 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2022-09-01 02:39 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-docs
On Thu, Aug 18, 2022 at 12:32:28PM -0400, Bruce Momjian wrote:
> On Wed, Aug 17, 2022 at 11:21:58PM -0400, Bruce Momjian wrote:
> > However, CREATE INDEX just lists the parameters, not the keywords, so it
> > was already there as 'method', as you mentioned above. I will just
> > remove the USING section I recently added.
>
> I wrote the attached patch to address the issues above.
Patch applied back to PG 10. Thanks for the tip.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-09-01 02:39 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-17 16:41 CREATE INDEX...USING Jeff Janes <jeff.janes@gmail.com>
2022-08-17 18:58 ` Bruce Momjian <bruce@momjian.us>
2022-08-17 21:11 ` Jeff Janes <jeff.janes@gmail.com>
2022-08-18 03:21 ` Bruce Momjian <bruce@momjian.us>
2022-08-18 16:32 ` Bruce Momjian <bruce@momjian.us>
2022-09-01 02:39 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox