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.
Best Answer
No.
That's actually not what that commit says. From the commit-message on the link you provided
That doesn't mean it's there now. It is PostgreSQL for "patches accepted." In fact that commit explicitly says,
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).