Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 5AC131337E75 for ; Sun, 16 Jan 2011 21:31:44 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 90992-02-4 for ; Mon, 17 Jan 2011 01:31:36 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtpsvr.ivp.ne.jp (smtpsvr.ivp.ne.jp [59.159.236.17]) by mail.postgresql.org (Postfix) with ESMTP id B7DB11337CC8 for ; Sun, 16 Jan 2011 21:25:26 -0400 (AST) Received: from IVPNAKAGAWAPC (ivpb.ivp.co.jp [219.96.232.34]) by smtpsvr.ivp.ne.jp (8.13.8/3.7W) with ESMTP id p0H1PMvn012137; Mon, 17 Jan 2011 10:25:22 +0900 Message-ID: <895509E8DA8F4649BBA565541CE333E9@IVPNAKAGAWAPC> From: =?utf-8?B?5Lit5bed44CA6Kqg6LK0?= To: "Dmitriy Igrishin" Cc: References: <49A94E2FF0F642B4ACBE09DB20CA5630@IVPNAKAGAWAPC><8294.1295027762@sss.pgh.pa.us><6A4B2F5F400049FEB3DF73B95BEA20EE@IVPNAKAGAWAPC> In-Reply-To: Subject: Re: why does seq scan instead of index scan Date: Mon, 17 Jan 2011 10:25:22 +0900 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0073_01CBB630.D8DC41F0" X-Priority: 3 X-MSMail-Priority: Normal Importance: Normal X-Mailer: Microsoft Windows Live Mail 14.0.8117.416 X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8117.416 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201101/43 X-Sequence-Number: 34990 This is a multi-part message in MIME format. ------=_NextPart_000_0073_01CBB630.D8DC41F0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Thank you very much. I will try hstore columns. Thanks. From: Dmitriy Igrishin=20 Sent: Sunday, January 16, 2011 1:36 AM To: =E4=B8=AD=E5=B7=9D =E8=AA=A0=E8=B2=B4=20 Cc: Tom Lane ; pgsql-sql@postgresql.org=20 Subject: Re: [SQL] why does seq scan instead of index scan Hey, To avoid creation of custom GiST-index you can use hstore, which supports GiST and GIN indexes. I propose you to add hstore column to the table and write trigger function which will generate hstore from array on insert or update. Or you can use hstore column instead of array. Please, see http://www.postgresql.org/docs/9.0/static/hstore.html 2011/1/15 =E4=B8=AD=E5=B7=9D=E3=80=80=E8=AA=A0=E8=B2=B4 = Thank you for your response. Are there any technique to index that query for like search on Text[] = Columns? Thanks. Nakagawa Maskai -------------------------------------------------- From: "Tom Lane" Sent: Saturday, January 15, 2011 2:56 AM To: "=E4=B8=AD=E5=B7=9D=E3=80=80=E8=AA=A0=E8=B2=B4" = Cc: Subject: Re: [SQL] why does seq scan instead of index scan=20 =3D?iso-2022-jp?B?GyRCQ2ZAbiEhQD81LhsoQg=3D=3D?=3D = writes: I'm trying to use like 'xx%' search on Text[] column. I thought it uses index scan. But actually it uses seq scan. Why? Those ANY expressions are not indexable. regards, tom lane --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql=20 --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql --=20 // Dmitriy. ------=_NextPart_000_0073_01CBB630.D8DC41F0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Thank you very much.

I will try hstore columns.

Thanks.

From: Dmitriy Igrishin

Sent: Sunday, January 16, 2011 1:36 AM

To: =E4=B8=AD=E5=B7=9D=20 =E8=AA=A0=E8=B2=B4

Cc: Tom=20 Lane ; pgsql-sql@postgresql.org =

Subject: Re: [SQL] why does seq scan instead of index=20 scan

Hey,

To avoid creation of custom GiST-index you = can use=20 hstore,
which supports GiST and GIN indexes.
I propose you to add = hstore=20 column to the table and write trigger
function which will generate = hstore=20 from array on insert or update.
Or you can use hstore column instead = of=20 array.
Please, see http://www= .postgresql.org/docs/9.0/static/hstore.html

2011/1/15 = =E4=B8=AD=E5=B7=9D=E3=80=80=E8=AA=A0=E8=B2=B4 <nakagawa@ivp.co.jp>
Thank you for your response.

Are there any = technique=20 to index that query for like search on Text[]=20 Columns?

Thanks.

Nakagawa=20 = Maskai

--------------------------------------------------
F= rom:=20 "Tom Lane" <tgl@sss.pgh.pa.us>
Sent: Saturday, January = 15, 2011=20 2:56 AM
To: "=E4=B8=AD=E5=B7=9D=E3=80=80=E8=AA=A0=E8=B2=B4" <nakagawa@ivp.co.jp>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: [SQL] = why does=20 seq scan instead of index scan=20

=3D?iso-2022-jp?B?GyRCQ2ZAbiEhQD81LhsoQg=3D=3D?=3D = <nakagawa@ivp.co.jp>=20 writes:
I'm trying to use like 'xx%' search on Text[]=20 column.
I thought it uses index scan. But actually it uses seq=20 scan.

Why?

Those ANY expressions = are not=20 indexable.

regards, tom lane

--
Sent via pgsql-sql = mailing=20 list (pgsql-sql@postgresql.org)
To make changes to = your=20 subscription:
http://www.postgresql.org/mailpref/pgsql-sql=20

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to = your=20 subscription:
http://www.postgresql.org/mailpref/pgsql-sql

--
// Dmitriy.

------=_NextPart_000_0073_01CBB630.D8DC41F0--