Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 312FC9FC160
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed,  9 May 2007 10:13:33 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 96547-02-3
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed,  9 May 2007 10:13:25 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from wr-out-0506.google.com (wr-out-0506.google.com
 [64.233.184.229])
	by postgresql.org (Postfix) with ESMTP id 8A9B09FC070
	for <pgsql-performance@postgresql.org>;
	Wed,  9 May 2007 10:12:47 -0300 (ADT)
Received: by wr-out-0506.google.com with SMTP id 70so185838wra
	for <pgsql-performance@postgresql.org>;
	Wed, 09 May 2007 06:12:46 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type;
	b=uRoJ9NBGSqVUR/6ACOyLbZshYx7pvmIJAirKUe10h7WB1FHKh/AftyQRJNe0tgQDAUgRhGi+J8+Zmhny7wf8q+5vmYOtdY8kJeYdxG0U1bGTzi74QAaRzlg1swiWFRXrgJkvVr7RsdymDwYpyTYgVhwtbFD2l/Fti5EkRaH0K5w=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=GnNhg8g9Ate4WeKNGe3VwfutlQQP3ezUSgilabBSiMaEuwUN7wlidzZHDfLZpDD9J+5ivsYkPI3NGTrK/k4JAPJgY8D54BTQY2WD/+4L2SthWWCNlJQNatChgUPeYH2SEVz0DCqUNYbkmrGP4Ht6jAOpUv5z0Ffh5YOnPNvPVS8=
Received: by 10.115.18.1 with SMTP id v1mr196503wai.1178716365675;
	Wed, 09 May 2007 06:12:45 -0700 (PDT)
Received: by 10.115.19.4 with HTTP; Wed, 9 May 2007 06:12:45 -0700 (PDT)
Message-ID: <3ce9822f0705090612nd6198a2xd06da85f1accbae9@mail.gmail.com>
Date: Wed, 9 May 2007 15:12:45 +0200
From: "Valentine Gogichashvili" <valgog@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Cannot make GIN intarray index be used by the planner
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_104634_12923603.1178716365175"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/183
X-Sequence-Number: 24573

------=_Part_104634_12923603.1178716365175
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_104634_12923603.1178716365175
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hello all, <br><br>I am trying to move from GiST intarray index to GIN intarray index, but my GIN index is not being used by the planner.<br><br>The normal query is like that <br><br><span style="font-family: courier new,monospace;">
select * <br>&nbsp; from sourcetablewith_int4 <br>&nbsp;where ARRAY[myint] &lt;@ myint_array <br>&nbsp;&nbsp; and some_other_filters</span><br><br>(with GiST index everything works fine, but GIN index is not being used)<br><br>If I create the same table populating it with text[] data like 
<br><br><span style="font-family: courier new,monospace;">select myint_array::text[] as myint_array_as_textarray </span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">&nbsp; into newtablewith_text 
</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">&nbsp; from sourcetablewith_int4</span><br style="font-family: courier new,monospace;"><br>and then create a GIN index using this new text[] column
<br><br>the planner starts to use the index and queries run with grate speed when the query looks like that:<br><br><span style="font-family: courier new,monospace;">select * <br>
&nbsp; from </span><span style="font-family: courier new,monospace;">newtablewith_text</span><span style="font-family: courier new,monospace;"> <br>
&nbsp;where ARRAY[&#39;myint&#39;] &lt;@ </span><span style="font-family: courier new,monospace;">myint_array_as_textarray</span><span style="font-family: courier new,monospace;"> <br>
&nbsp;&nbsp; and some_other_filters</span><br><br>Where the problem can be with _int4 GIN index in this constellation? <br><br><span>by now the <span style="font-family: courier new,monospace;">enable_seqscan</span> i</span><span>
s set to off in the configuration.<br><br>With best regards, <br><br>-- Valentine Gogichashvili</span><br>

------=_Part_104634_12923603.1178716365175--