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.
Our team is looking into more efficient and scalable query approach than the
intarrayextension with GIN index lookup. We are currently doing performance testing withpg_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.
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
bitmapGIN 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.
In this case, it is okay for not having GIN index support because the SQL is not selective to begin with, and the
pg_roaringbitmapspeed boost is very helpful in filtering records quickly.Our concern is mainly with using
pg_roaringbitmapfor use case 1 above.