CREATE INDEX...USING

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

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

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

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

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