5

BRIN indexes seem useful, but I'm not sure how to use one on an ENUM type. I thought this code would work:

CREATE TYPE test_enum AS ENUM ('a', 'b');
CREATE TEMPORARY TABLE my_table (
    x test_enum
);

CREATE INDEX test_index ON my_table using brin (x);
ERROR:  data type test_enum has no default operator class for access method "brin"

Do I have to create a new operator class from scratch? Aren't enums already ordered?

This commit, from 2014, implies that BRIN indexes should work for ENUM types.

  • Version 10.6 and 11.1 – karldw Dec 18 '18 at 1:56
5

Aren't enums already ordered?

No.

This commit, from 2014, implies that BRIN indexes should work for ENUM types.

That's actually not what that commit says. From the commit-message on the link you provided

This type of operator class we call "Minmax", and we supply a bunch of them for most data types with B-tree opclasses. Since the BRIN code is generalized, other approaches are possible for things such as arrays, geometric types, ranges, etc; even for things such as enum types we could do something different than minmax with better results. In this commit I only include minmax.

That doesn't mean it's there now. It is PostgreSQL for "patches accepted." In fact that commit explicitly says,

+/* no brin opclass for enum, tsvector, tsquery, jsonb, range */

Actually creating a BRIN index for an ENUM is, afaik, going to require some use of C and knowledge of index operator class implementation (pick/split/insert/merge).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.