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@xxxxxxxxxx> 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@xxxxxxxxxx> 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@xxxxxxxxxx, 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@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
ვალენტინ გოგიჩაშვილი
Valentine Gogichashvili
