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 =
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;
"versionA", public
(1 row)

With best regards,

-- Valentine

On 5/9/07, Tom Lane <[EMAIL PROTECTED]> wrote:

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

"Valentine Gogichashvili" <[EMAIL PROTECTED]> 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 =

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

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

Reply via email to