1

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.

2
  • It is hard to imagine this actually being useful. Maybe the planner correctly understands the futility of using the index.
    – jjanes
    May 29, 2023 at 10:29
  • While testing bloom indexes on bigint columns, I found them to be very slow. I'm trying GIN indexes via btree_gin, but I need to cast values as ::bigint. I'm aiming to index all foreign keys (~500) in a large database to avoid full table scans during cascading deletes. While BTree indexes work, they consume a lot of storage. I'm exploring GIN indexes for a more space-efficient solution.
    – Sri S
    Jun 5, 2023 at 3:48

2 Answers 2

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

1

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);
3
  • This works for bloom index on bigint columns. I'm now trying out gin using btree_gin extension. Again, I have to explicitly cast the values like ::bigint. Is there a solution for gin as well? The use case is we're trying to create indexes on all the foreign keys of a large database (total ~500 foreign keys) with some tables spanning ~500 million rows to prevent the sequential scan of the referred table while deleting (cascade). Using BTree indexes on every column works, but it would be better if we could reduce the storage space occupied by the indexes.
    – Sri S
    Jun 5, 2023 at 3:42
  • It's mentioned (attached link) that GIN indexes proved to be efficient for this use case. So, we would like to try it out. dba.stackexchange.com/questions/265508/…
    – Sri S
    Jun 5, 2023 at 3:50
  • That's a different question. Jun 5, 2023 at 6:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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