Postgres Pro
Downloads
Home   >   mailing lists

Re: Cannot make GIN intarray index be used by the planner - Mailing list pgsql-performance

From Valentine Gogichashvili
Subject Re: Cannot make GIN intarray index be used by the planner
Date
Msg-id 3ce9822f0705091141m227697d7h4b7f2b5b723cfbd7@mail.gmail.com
Whole thread Raw
In response to Re: Cannot make GIN intarray index be used by the planner  (Oleg Bartunov <oleg@sai.msu.su>)
Responses Re: Cannot make GIN intarray index be used by the planner
List pgsql-performance
Tree view
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



On 5/9/07, Oleg Bartunov <oleg@sai.msu.su> wrote:
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



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

pgsql-performance by date:

Previous
From: david@lang.hm
Date:
Subject: Re: ZFS and Postgresql - WASRe: Best OS for Postgres 8.2
Next
From: Tom Lane
Date:
Subject: Re: Cannot make GIN intarray index be used by the planner