Re: Overloaded && operator from intarray module prevents index usage.

On Thu, Feb 28, 2019 at 3:34 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Michael Lewis <mlewis(at)entrata(dot)com> writes:
> > Can you dumb down how to change the index or column type such that an
> index
> > will be used for the && operator while intarray extension is installed?
> We
> > have the intarray extension installed and I doubt that I can get it
> > removed.
>
> There's no magic nice solution to this, or we'd have told you about it.
>
> Possible options:
>
> 1. Remove intarray extension.
> 2. Move intarray extension to a schema that's not in your search path.
> 3. Create an index using intarray's opclass, instead of or in addition
> to the core-opclass index.
>

Thank you so much for you time in enumerating the options. What's the
concern/problem/cost to re-creating the index with the intarray's opclass?
If that's what will be used by && with the extension installed, then what's
the downside?

I see significant code refactor for option 1 and 2 as it will have system
wide impact rather than specific to the use of this particular column which
is limited.

> 4. Rename intarray's && operator to something else (will bite you at
> next dump/reload, where the renaming will be lost).
> 5. Always schema-qualify references to the core && operator.
>

Would a sixth option be to re-create the column as array type and keep the
index as is and ensure that in queries, I am using conditions like ARRAY[1]
&& table.column_name rather than '{1}'::integer[] && table.column_name? Or
would I still need to schema qualify references to the core && operator for
it to be used?

If I created a table and disabled sequential scan and such, I suppose I
could easily test that the index get used or not.

>
> All of these have obvious downsides, especially if you're actively
> using the intarray extension for other purposes.
>
> regards, tom lane
>