Back to Top

Thursday, June 05, 2008

Why isn't my GIN index being used in Postgres after I install the _int contrib package?

While working with some GIN indexes (to speed up the && and <@ / @> operators) I was very surprised to see that after the installation of the intarray (_int) contrib module the query planner stopped using the index. After some poking around it came to me:

The contrib module is defining its own operators for the arrays, operators which are different from the ones used by GIN to build the indexes. My quick and dirty solution is to drop the functions which implement those operators with cascade, which results in the operators themselves being dropped also:

DROP FUNCTION _int_overlap(integer[], integer[]) CASCADE;

DROP FUNCTION _int_contained(integer[], integer[]) CASCADE;

DROP FUNCTION _int_contains(integer[], integer[]) CASCADE;

This drops the functions which implement the aforementioned three operators. And now everything works again. A word of caution: probably there are less destructive methods to resolve this problem (possibly recreating the index with an other operator class or something like that), however this worked for me.


Post a Comment

You can use some HTML tags, such as <b>, <i>, <a>. Comments are moderated, so there will be a delay until the comment appears. However if you comment, I follow.