Re: Cannot make GIN intarray index be used by the planner - Mailing list 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