Cannot make GIN intarray index be used by the planner

From: "Valentine Gogichashvili" <valgog(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Cannot make GIN intarray index be used by the planner
Date: 2007-05-09 13:12:45
Message-ID: 3ce9822f0705090612nd6198a2xd06da85f1accbae9@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Oleg Bartunov 2007-05-09 13:31:19 Re: Cannot make GIN intarray index be used by the planner
Previous Message Dave Page 2007-05-09 13:01:56 Re: PostgreSQL wants to install, cancel or allow? (was Re: Windows Vista support (Buildfarm Vaquita)

Browse pgsql-performance by date

  From Date Subject
Next Message Andrzej Zawadzki 2007-05-09 13:22:41 Poor performance with queries using clause: sth IN (...)
Previous Message Peter Eisentraut 2007-05-09 12:10:57 Apparently useless bitmap scans