From: Valentine Gogichashvili <valgog@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Oleg Bartunov <oleg@sai.msu.su>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] Cannot make GIN intarray index be used by the planner
Date: Thu, 10 May 2007 11:07:17 +0200
Message-ID: <3ce9822f0705100207q2e526936gac831901d577e8e2@mail.gmail.com> (raw)
In-Reply-To: <29856.1178738292@sss.pgh.pa.us>
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>
<29856.1178738292@sss.pgh.pa.us>
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 <tgl@sss.pgh.pa.us> wrote:
>
> [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
>
--
ვალენტინ გოგიჩაშვილი
Valentine Gogichashvili
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: valgog@gmail.com, tgl@sss.pgh.pa.us, oleg@sai.msu.su, pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] Cannot make GIN intarray index be used by the planner
In-Reply-To: <3ce9822f0705100207q2e526936gac831901d577e8e2@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox