I can't use directly uuid with gist index
CREATE INDEX idx_leaderboads_values_gist
ON leaderboard_entry
USING gist
(id_leaderboard , value);
And I got this error:
ERROR: data type uuid has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.
btree_gist now also covers the data type uuid, like Paul commented. (And some other data types, notably all enum types.)
Now all you need is to install the extension once per database:
CREATE EXTENSION btree_gist;
Then your index should just work.
Related:
(Original answer.)
Normally I would suggest the additional module btree_gist, but the type uuid is not covered by it.
In theory, since a UUID is a 128-bit quantity (per documentation), the most efficient way would be to convert it to two bigint or float8 for the purpose of the index. But none of these casts are defined in standard Postgres.
I found a stab in that direction in the pqsql-hackers list, but it seems unsuccessful.
The remaining option is a functional GiST index on the text representation:
CREATE INDEX idx_leaderboads_values_gist
ON leaderboard_entry USING gist (id_leaderboard, cast("value" AS text));
To make use of this functional index, queries must match that expression. You can use the shorthand "value"::text in queries (but not in the index definition without adding more parentheses).
Aside: do not use value as column name it's a reserved word in standard SQL.
The question is: why do you need the GiST index. The best solution depends on the objective.
btree_gist supports UUIDs, so you don't need to cast anymore.
If you're using Postgres 10, and you migrated your database from a previous version using dump/restore, you may need to run:
ALTER EXTENSION btree_gist UPDATE;
in order to get gist indexes to work with UUID's.
For anyone having one this error message using Django:
UndefinedObject: data type uuid has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.
The above exception was the direct cause of the following exception:
ProgrammingError: data type uuid has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.
The best way to install the extension (reversibly) is to edit your migrations files, this is what you need:
# 0000_GistIndex.py
from django.contrib.postgres.operations import BtreeGistExtension
from django.db import migrations
class Migration(migrations.Migration):
initial = True
dependencies = []
operations = [BtreeGistExtension()]
And then, add 0000_GistIndex to the dependencies of 0001_initial.py.
Tested on Django 5.1. Link to the documentation.
I know this answer is more specific than what the OP was looking for, but that page was the first result on a popular search engine.
