Thread: Cannot make GIN intarray index be used by the planner

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 i s set to off in the configuration.

With best regards,

-- Valentine Gogichashvili

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




--
ვალენტინ გოგიჩაშვილი
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

Hi again,

the version of the server I am on is PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.2 20050901 (prerelease) (SUSE Linux)

here is the DT

CREATE TABLE "versionA".myintarray_table_nonulls
(
  id integer,
  myintarray_int4 integer[]
)
WITHOUT OIDS;

CREATE INDEX idx_nonnulls_myintarray_int4_gin
  ON "versionA".myintarray_table_nonulls
  USING gin
  (myintarray_int4);

there are 745989 records in the table with no null values for the myintarray_int4 field.

So here is the execution plan

myvideoindex=# explain analyze SELECT id, icount(myintarray_int4)
  FROM "versionA".myintarray_table_nonulls
 WHERE ARRAY[8] <@ myintarray_int4;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on myintarray_table_nonulls  (cost=100000000.00..100015267.73 rows=746 width=32) (actual time=0.079..1156.393 rows=28207 loops=1)
   Filter: ('{8}'::integer[] <@ myintarray_int4)
 Total runtime: 1266.346 ms
(3 rows)

Then I drop the GIN and create a GiST index

DROP INDEX "versionA".idx_nonnulls_myintarray_int4_gin;

CREATE INDEX idx_nonnulls_myintarray_int4_gist
  ON "versionA".myintarray_table_nonulls
  USING gist
  (myintarray_int4);

and here are the results for the execution plan

myvideoindex=# explain analyze SELECT id, icount(myintarray_int4)
myvideoindex-#   FROM "versionA".myintarray_table_nonulls
myvideoindex-#  WHERE ARRAY[8] <@ myintarray_int4;
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on myintarray_table_nonulls  (cost=42.36..2137.62 rows=746 width=32) (actual time=154.276..301.615 rows=28207 loops=1)
   Recheck Cond: ('{8}'::integer[] <@ myintarray_int4)
   ->  Bitmap Index Scan on idx_nonnulls_myintarray_int4_gist  (cost= 0.00..42.17 rows=746 width=0) (actual time=150.713..150.713 rows=28207 loops=1)
         Index Cond: ('{8}'::integer[] <@ myintarray_int4)
 Total runtime: 410.394 ms
(5 rows)

As you can see the index is in use...

Now I create create the same table with myintarray_int4 converted into text array and create a GIN index on the new text array field

SELECT id, myintarray_int4::text[] as myintarray_int4_text into myintarray_table_nonulls_text from myintarray_table_nonulls;

CREATE INDEX idx_nonnulls_myintarray_int4_text_gin
  ON "versionA".myintarray_table_nonulls_text
  USING gin
  (myintarray_int4_text);

and have a table with DT:

CREATE TABLE "versionA".myintarray_table_nonulls_text
(
  id integer,
  myintarray_int4_text text[]
)
WITHOUT OIDS;

Now the same request has the following execution plan:

myvideoindex=# explain analyze SELECT id, array_upper( myintarray_int4_text, 1 )
  FROM "versionA".myintarray_table_nonulls_text
 WHERE ARRAY['8'] <@ myintarray_int4_text;
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on myintarray_table_nonulls_text  (cost=10.06..2136.97 rows=746 width=37) (actual time=17.463..191.094 rows=28207 loops=1)
   Recheck Cond: ('{8}'::text[] <@ myintarray_int4_text)
   ->  Bitmap Index Scan on idx_nonnulls_myintarray_int4_text_gin  (cost=0.00..9.87 rows=746 width=0) (actual time=13.982..13.982 rows=28207 loops=1)
         Index Cond: ('{8}'::text[] <@ myintarray_int4_text)
 Total runtime: 303.348 ms
(5 rows)


I hope this information will make the question more understandable.

With best regards,

-- Valentine






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

[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




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