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 On 5/9/07, Tom Lane <[EMAIL PROTECTED]> wrote:
[cc'ing to pgsql-hackers since this is looking like a contrib/intarray bug] "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 = '"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
-- ვალენტინ გოგიჩაშვილი Valentine Gogichashvili