Обсуждение: 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
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
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
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
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
[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
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