How to use uuid with postgresql gist index type?

Question

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.

Warplane

yes I have my anwser – Warplane

Answer 1

Postgres 10 or newer

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:

• Exclusion constraint on a bitstring column with bitwise AND operator
• Creating multicolumn index in PostgreSQL, containing both scalar and array columns

Postgres 9.6 or older

(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.

Jinnah

I need to lookup value around one value in the leaderboard and I want to avoid to create two query to do it. – Warplane

Now that Postgres 10 is out, btree_gist supports UUIDs, so you don't need to cast anymore. – Aleda

@Paul: Great! Thanks for the update! I assume you are the author of the patch, too? Kudos! – Jinnah

Ha ha I originated it but several others got it over the finish line. :-) – Aleda

Answer 2

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.

Duodenary

Thanks! I think you just saved our deploy :D – Toshiatoshiko