Cannot make GIN intarray index be used by the planner

Do you have both indexes (GiST, GIN) on the same table ?

On Wed, 9 May 2007, Valentine Gogichashvili wrote:

Hello all,

I am trying to move from GiST intarray index to GIN intarray index, but my
GIN index is not being used by the planner.

The normal query is like that

select *
from sourcetablewith_int4
where ARRAY[myint] <@ myint_array
and some_other_filters

(with GiST index everything works fine, but GIN index is not being used)

If I create the same table populating it with text[] data like

select myint_array::text[] as myint_array_as_textarray
into newtablewith_text
from sourcetablewith_int4

and then create a GIN index using this new text[] column

the planner starts to use the index and queries run with grate speed when
the query looks like that:

select *
from newtablewith_text
where ARRAY['myint'] <@ myint_array_as_textarray
and some_other_filters

Where the problem can be with _int4 GIN index in this constellation?

by now the enable_seqscan is set to off in the configuration.

With best regards,

-- Valentine Gogichashvili

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

I have experimented quite a lot. So first I did when starting the attempt to
move from GiST to GIN, was to drop the GiST index and create a brand new GIN
index... after that did not bring the results, I started to create all this
tables with different sets of indexes and so on...

So the answer to the question is: no there in only GIN index on the table.

Thank you in advance,

Valentine

On 5/9/07, Oleg Bartunov <oleg@sai.msu.su> wrote:

Do you have both indexes (GiST, GIN) on the same table ?

On Wed, 9 May 2007, Valentine Gogichashvili wrote:

Hello all,

I am trying to move from GiST intarray index to GIN intarray index, but

my

GIN index is not being used by the planner.

The normal query is like that

select *
from sourcetablewith_int4
where ARRAY[myint] <@ myint_array
and some_other_filters

(with GiST index everything works fine, but GIN index is not being used)

If I create the same table populating it with text[] data like

select myint_array::text[] as myint_array_as_textarray
into newtablewith_text
from sourcetablewith_int4

and then create a GIN index using this new text[] column

the planner starts to use the index and queries run with grate speed

when

the query looks like that:

select *
from newtablewith_text
where ARRAY['myint'] <@ myint_array_as_textarray
and some_other_filters

Where the problem can be with _int4 GIN index in this constellation?

by now the enable_seqscan is set to off in the configuration.

With best regards,

-- Valentine Gogichashvili

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

On Wed, 9 May 2007, Valentine Gogichashvili wrote:

I have experimented quite a lot. So first I did when starting the attempt to
move from GiST to GIN, was to drop the GiST index and create a brand new GIN
index... after that did not bring the results, I started to create all this
tables with different sets of indexes and so on...

So the answer to the question is: no there in only GIN index on the table.

then, you have to provide us more infomation -
pg version,
\dt sourcetablewith_int4
explain analyze

btw, I did test of development version of GiN, see
http://www.sai.msu.su/~megera/wiki/GinTest

Thank you in advance,

Valentine

On 5/9/07, Oleg Bartunov <oleg@sai.msu.su> wrote:

Do you have both indexes (GiST, GIN) on the same table ?

On Wed, 9 May 2007, Valentine Gogichashvili wrote:

Hello all,

I am trying to move from GiST intarray index to GIN intarray index, but

my

GIN index is not being used by the planner.

The normal query is like that

select *
from sourcetablewith_int4
where ARRAY[myint] <@ myint_array
and some_other_filters

(with GiST index everything works fine, but GIN index is not being used)

If I create the same table populating it with text[] data like

select myint_array::text[] as myint_array_as_textarray
into newtablewith_text
from sourcetablewith_int4

and then create a GIN index using this new text[] column

the planner starts to use the index and queries run with grate speed

when

the query looks like that:

select *
from newtablewith_text
where ARRAY['myint'] <@ myint_array_as_textarray
and some_other_filters

Where the problem can be with _int4 GIN index in this constellation?

by now the enable_seqscan is set to off in the configuration.

With best regards,

-- Valentine Gogichashvili

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

[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

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