Custom Operator Class for BigInt Using Bloom Indexes Not Working in PostgreSQL

Question

I'm currently dealing with a large PostgreSQL table containing approximately 350 million rows. For performance optimization, I'm trying to create Bloom indexes on some bigint columns in my table.

Since PostgreSQL doesn't natively support Bloom indexes for bigint, I've created a custom operator class using the following SQL:

CREATE OPERATOR CLASS bigint_ops
DEFAULT FOR TYPE bigint USING bloom AS
    OPERATOR    1   =(bigint, bigint),
    FUNCTION    1   hashint8(bigint);

Despite this, when I create Bloom indexes on bigint columns, it seems that PostgreSQL is not using these indexes. Conversely, when I create Bloom indexes on int columns, which PostgreSQL supports by default, the system successfully uses the created indexes.

I'm not sure why my custom operator class isn't being used for bigint columns. I've been trying to figure out what might be the issue, but so far I've come up short.

Could anyone provide some insight as to why this might be happening? Is there a special step to make PostgreSQL use my custom operator class for bigint?

Any help or insights would be greatly appreciated.

Answer 1

The extensible indexes are very fussy about their data types. While where bigintcol=17 will work for built-in index access methods, for bloom you would have to write it as where bigintcol=17::bigint

Answer 2

Th other answer shows you a workaround, but the proper solution is to create operator classes to compare with integer and smallint as well:

CREATE OPERATOR FAMILY bigint_ops USING bloom;
                                       
CREATE OPERATOR CLASS int8_ops
DEFAULT FOR TYPE bigint USING bloom
FAMILY bigint_ops AS
    OPERATOR    1   =(bigint, bigint),
    FUNCTION    1   hashint8(bigint);

CREATE OPERATOR CLASS int84_ops
FOR TYPE integer USING bloom
FAMILY bigint_ops AS
    OPERATOR    1   =(bigint, integer);

CREATE OPERATOR CLASS int82_ops
FOR TYPE smallint USING bloom
FAMILY bigint_ops AS
    OPERATOR    1   =(bigint, smallint);