Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id AB58363349A for ; Wed, 25 Feb 2009 07:04:44 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 78310-10 for ; Wed, 25 Feb 2009 07:04:37 -0400 (AST) X-Greylist: delayed 00:05:07.23798 by SQLgrey-1.7.6 Received: from wf-out-1314.google.com (wf-out-1314.google.com [209.85.200.169]) by mail.postgresql.org (Postfix) with ESMTP id BF46A63286F for ; Wed, 25 Feb 2009 07:04:37 -0400 (AST) Received: by wf-out-1314.google.com with SMTP id 23so3585585wfg.28 for ; Wed, 25 Feb 2009 03:04:36 -0800 (PST) MIME-Version: 1.0 Received: by 10.142.157.9 with SMTP id f9mr3080295wfe.241.1235559567193; Wed, 25 Feb 2009 02:59:27 -0800 (PST) Date: Wed, 25 Feb 2009 18:59:27 +0800 Message-ID: <8f750b7c0902250259w6065515as350aca3b5d7d8173@mail.gmail.com> Subject: "like" and index From: Tony Liao To: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd2de4619f8750463bc2169 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200902/207 X-Sequence-Number: 31428 --000e0cd2de4619f8750463bc2169 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit hi all, I have a table table_A (id serial,prefix varchar),for example. now I want to get the id of "johnsmith"'s prefix match table_A.prefix,so I do select id from table_A where 'johnsmith' like prefix||'%' ,the table_A is very large so I would like to make index. create table_A_index on table_A(prefix) I try to explain analyze,but it doesn't work ,it use seq scan. I try another index. drop index table_A_index; create table_A_index on table_A(prefix varchar_pattern_ops); it doesn't work,too. thanks ps:I have another table table_B would use table_B.prefix= table_A.prefix.so how can I create the index? yours, Tony --000e0cd2de4619f8750463bc2169 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable hi all,
=A0=A0=A0=A0=A0 I have a table table_A (id serial,prefix varchar= ),for example.
=A0=A0=A0=A0=A0 now I want to get the id of "johnsmi= th"'s prefix match table_A.prefix,so I do select id from table_A w= here 'johnsmith' like prefix||'%'=A0 ,the table_A is very l= arge so I would like to make index. create table_A_index on table_A(prefix)=
=A0 =A0 =A0 I try to explain analyze,but it doesn't work ,it use seq sc= an.
=A0=A0=A0=A0=A0 I try another index. drop index table_A_index; creat= e table_A_index on table_A(prefix varchar_pattern_ops); it doesn't work= ,too.
=A0=A0=A0=A0=A0 thanks
=A0=A0=A0=A0=A0=A0 ps:I have another table table_B would use table_B.prefix= =3Dtable_A.prefix.so how can I cre= ate the index?





=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 yours,= Tony
--000e0cd2de4619f8750463bc2169--