Thread: Re: [PERFORM] Cannot make GIN intarray index be used by the planner

[cc'ing to pgsql-hackers since this is looking like a contrib/intarray bug]

"Valentine Gogichashvili" <valgog@gmail.com> writes:
> here is the DT

That works fine for me in 8.2:

regression=#  explain SELECT id, (myintarray_int4)
  FROM myintarray_table_nonulls
 WHERE ARRAY[8] <@ myintarray_int4;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_nonnulls_myintarray_int4_gin on myintarray_table_nonulls  (cost=0.00..8.27 rows=1 width=36)
   Index Cond: ('{8}'::integer[] <@ myintarray_int4)
(2 rows)

What I am betting is that you've installed contrib/intarray in this
database and that's bollixed things up somehow.  In particular, intarray
tries to take over the position of "default" gin opclass for int4[],
and the opclass that it installs as default has operators named just
like the built-in ones.  If somehow your query is using pg_catalog.<@
instead of intarray's public.<@, then the planner wouldn't think the
index is relevant.

In a quick test your example still works with intarray installed, because
what it's really created is public.<@ (integer[], integer[]) which is
an exact match and therefore takes precedence over the built-in
pg_catalog.<@ (anyarray, anyarray).  But if for example you don't have
public in your search_path then the wrong operator would be chosen.

Please look at the pg_index entry for your index, eg

select * from pg_index where indexrelid =
'"versionA".idx_nonnulls_myintarray_int4_gin'::regclass;

and see whether the index opclass is the built-in one or not.

Note to hackers: we've already discussed that intarray shouldn't be
trying to take over the default gin opclass, but I am beginning to
wonder if it still has a reason to live at all.  We should at least
consider removing the redundant operators to avoid risks like this one.

            regards, tom lane

Hello again,

I got the opclass for the index and it looks like it is a default one

myvideoindex=# select pg_opclass.*, pg_type.typname
myvideoindex-#   from pg_index, pg_opclass, pg_type
myvideoindex-#  where pg_index.indexrelid = 'idx_nonnulls_myintarray_int4_gin'::regclass
myvideoindex-#    and pg_opclass.oid = any (pg_index.indclass::oid[] )
myvideoindex-#    and pg_type.oid = pg_opclass.opcintype;

 opcamid |  opcname  | opcnamespace | opcowner | opcintype | opcdefault | opckeytype | typname
---------+-----------+--------------+----------+-----------+------------+------------+---------
    2742 | _int4_ops |           11 |       10 |      1007 | t          |         23 | _int4
(1 row)

The search_path is set to the following

myvideoindex=# show search_path;
    search_path
--------------------
 "versionA", public
(1 row)

With best regards,

-- Valentine




--
ვალენტინ გოგიჩაშვილი
Valentine Gogichashvili