Support for Pg index lookup

[tonywong-com]

Our team is looking into more efficient and scalable query approach than the intarray extension with GIN index lookup. We are currently doing performance testing with pg_roaringbitmap, and we couldn't find any reference on GIN index support.

We are wondering if there is any Pg index support, or GIN operator support in the technical roadmap?

---

We have two main SQL use cases for looking up a page of content among 10 million records, with average bitmap cardinality of 10,000.

Use case 1: lookup records by a small array of id's, in this case 2 id's.

SELECT * FROM records WHERE bitmap && roaringbitmap('{30,38}') LIMIT 25;

Because of the lower frequency of the id 30 and 38 among the records' bitmaps. This result in an expensive SeqScan with bitmap Filter before the query planner finds 25 records. It would be great if we can do quick look-up with a bitmap GIN index alone.

Use case 2: lookup records by a large array of id's, in this case 9,900 id's, which is very close to the bitmap cardinality.

SELECT * FROM records WHERE bitmap && roaringbitmap('{1,2,....,9900}') LIMIT 25;

In this case, it is okay for not having GIN index support because the SQL is not selective to begin with, and the pg_roaringbitmap speed boost is very helpful in filtering records quickly.

---

Our concern is mainly with using pg_roaringbitmap for use case 1 above.

[ajcampa]

We have the same problem or need. We need to use an index but we recive the error "data type roaringbitmap has no default operator class for access method 'gist'" even even in PostgreSQL 15. I understand that the roaringbitmap data type defined by the pg_roaringbitmap extension does not have a specific operator class implemented for GIST or BTREE access methods...