Clever Geek Handbook

Can GIN or GIST be used to compare "containing at least"?

I am using intarray extension to compare int since 9.2.9 release.

While one &&

can use an index to filter arrays that contain at least one value, is there a way to use a GIN or GIST index to filter arrays with at least n overlapping values?

For example, how do GIN or GIST use 2 <= icount(ARRAY[1,2,3] & int_array_column)

or 2 <= ARRAY_UPPER((ARRAY[1,2,3] & int_array_column),1)

?

+3


source to share


2 answers


The smlar extension from Teodor Sigaev has what they need. The operator %

indexes by n intersecting values.



+1


source


A btree will be able to index your expression and then filter the number of values ​​you need:



CREATE INDEX my_awesome_ids 
ON my_fat_table (icount(ARRAY[1,2,3] & int_array_column)); 

SELECT * 
FROM my_fat_table 
WHERE 2 <= (icount(ARRAY[1,2,3] & int_array_column));
0


source






More articles:


All Articles