How to add a PostgreSQL GIN index on a uuid in Postgres 9.6

Question

I'm trying to add a GIN index that includes a UUID in a Postgres 9.6 database. Technically it is a composite index, with composite GIN support coming from the btree_gin plugin.

I try to create the index with this statement:

CREATE EXTENSION btree_gin;
CREATE INDEX ix_tsv ON text_information USING GIN (client_id, text_search_vector);

but I get this error:

ERROR: data type uuid has no default operator class for access method "gin"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

client_id is data type uuid and text_search_vector is a tsvector. I don't think the composite/btree_gin factor is actually relevant, as I get the same error trying to create the index on just client_id alone, but hopefully if there is a solution to this, it is one that will work with a composite index also.

I found PostgreSQL GIN index on array of uuid , which seems to suggest that it should be possible (if an array of UUIDs can be done, then surely an individual UUID can be done). However, the solution there was pretty opaque to me - it's not immediately obvious how to modify this solution to support a single UUID.

I would prefer a solution that doesn't involve casting the UUID to another type in the index or in another column, as I would rather not have to write specialized queries with casts in them (we are using django ORM to generate queries atm).

Answer 1

It is possible for GIN indexes. But not before Postgres 11, where it was added. The release notes:

Allow btree_gin to index bool, bpchar, name and uuid data types (Matheus Oliveira)

So the simple solution is to upgrade to Postgres 11. This should be good news for you:

April 9, 2019: Cloud SQL now supports PostgreSQL version 11.1 Beta

Or, in many cases you can alternatively use a GiST index, for which the same was introduced with Postgres 10, already. The release notes:

Add indexing support to btree_gist for the UUID data type (Paul Jungwirth)

Related:

• How to use uuid with postgresql gist index type?

If neither is an option, you are back to what you wanted to avoid:

casting the uuid to another type in the index

You can create an expression index on a (consistent!) text representation or, theoretically, on two bigint columns derived from the uuid. But the first makes the index considerably bigger and slower and the second creates much more complication ...

The syntax of the cast is simple enough though: uuid::text. In an index expression that requires an extra set of parentheses. With the additional module btree_gin installed:

CREATE INDEX ix_uuid_tsv ON text_information USING GIN ((client_id::uuid), tsv);

Related:

• Postgres using an index for one table but not another
• What is the optimal data type for an MD5 field?
• Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

Or you could backport the feature from Postgres 11 - which is not an option with a hosted service like Google Cloud SQL for PostgreSQL as you mentioned in a comment. And I hardly see the use case where one would be skilled enough to implement the backport, but not to upgrade to Postgres 11.