pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCannot make GIN intarray index be used by the planner
6+ messages / 3 participants
[nested] [flat]
* Cannot make GIN intarray index be used by the planner
@ 2007-05-09 13:12 Valentine Gogichashvili <valgog@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Valentine Gogichashvili @ 2007-05-09 13:12 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Cannot make GIN intarray index be used by the planner
@ 2007-05-09 13:31 Oleg Bartunov <oleg@sai.msu.su>
parent: Valentine Gogichashvili <valgog@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Oleg Bartunov @ 2007-05-09 13:31 UTC (permalink / raw)
To: Valentine Gogichashvili <valgog@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Cannot make GIN intarray index be used by the planner
@ 2007-05-09 13:36 Valentine Gogichashvili <valgog@gmail.com>
parent: Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 1 reply; 6+ messages in thread
From: Valentine Gogichashvili @ 2007-05-09 13:36 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Cannot make GIN intarray index be used by the planner
@ 2007-05-09 13:49 Oleg Bartunov <oleg@sai.msu.su>
parent: Valentine Gogichashvili <valgog@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Oleg Bartunov @ 2007-05-09 13:49 UTC (permalink / raw)
To: Valentine Gogichashvili <valgog@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PERFORM] Cannot make GIN intarray index be used by the planner
@ 2007-05-09 19:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2007-05-09 19:18 UTC (permalink / raw)
To: Valentine Gogichashvili <valgog@gmail.com>; +Cc: Oleg Bartunov <oleg@sai.msu.su>; pgsql-performance; pgsql-hackers@postgresql.org
[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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PERFORM] Cannot make GIN intarray index be used by the planner
@ 2007-05-10 09:07 Valentine Gogichashvili <valgog@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Valentine Gogichashvili @ 2007-05-10 09:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Oleg Bartunov <oleg@sai.msu.su>; pgsql-performance; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-05-10 09:07 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-09 13:12 Cannot make GIN intarray index be used by the planner Valentine Gogichashvili <valgog@gmail.com>
2007-05-09 13:31 ` Oleg Bartunov <oleg@sai.msu.su>
2007-05-09 13:36 ` Valentine Gogichashvili <valgog@gmail.com>
2007-05-09 13:49 ` Oleg Bartunov <oleg@sai.msu.su>
2007-05-09 19:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-10 09:07 ` Valentine Gogichashvili <valgog@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox