To: "Valentine Gogichashvili" <valgog@gmail.com>
cc: "Oleg Bartunov" <oleg@sai.msu.su>, pgsql-performance@postgresql.org,
	pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] Cannot make GIN intarray index be used by the planner 
In-reply-to: <3ce9822f0705091141m227697d7h4b7f2b5b723cfbd7@mail.gmail.com> 
References: <3ce9822f0705090612nd6198a2xd06da85f1accbae9@mail.gmail.com>
	<Pine.LNX.4.64.0705091728560.12152@sn.sai.msu.ru>
	<3ce9822f0705090636s46b71c48mafb6ba7d64781fbf@mail.gmail.com>
	<Pine.LNX.4.64.0705091744470.12152@sn.sai.msu.ru>
	<3ce9822f0705091141m227697d7h4b7f2b5b723cfbd7@mail.gmail.com>
Comments: In-reply-to "Valentine Gogichashvili" <valgog@gmail.com>
	message dated "Wed, 09 May 2007 20:41:07 +0200"
Date: Wed, 09 May 2007 15:18:12 -0400
Message-ID: <29856.1178738292@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

[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