Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 2B2726333DA for ; Thu, 26 Feb 2009 01:24:28 -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 50138-02 for ; Thu, 26 Feb 2009 01:24:20 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from wf-out-1314.google.com (wf-out-1314.google.com [209.85.200.175]) by mail.postgresql.org (Postfix) with ESMTP id 067A2633204 for ; Thu, 26 Feb 2009 01:24:18 -0400 (AST) Received: by wf-out-1314.google.com with SMTP id 23so418303wfg.28 for ; Wed, 25 Feb 2009 21:24:17 -0800 (PST) MIME-Version: 1.0 Received: by 10.142.147.15 with SMTP id u15mr477584wfd.47.1235625857120; Wed, 25 Feb 2009 21:24:17 -0800 (PST) In-Reply-To: References: <8f750b7c0902250259w6065515as350aca3b5d7d8173@mail.gmail.com> Date: Thu, 26 Feb 2009 13:24:17 +0800 Message-ID: <8f750b7c0902252124k70361392x1992852c1e76e04d@mail.gmail.com> Subject: Re: "like" and index From: Tony Liao To: Harald Fuchs Cc: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd28d5249f8eb0463cb90e0 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/224 X-Sequence-Number: 31445 --000e0cd28d5249f8eb0463cb90e0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit thanks,everybody! Harald,you understand correctly.I downloaded the prefix contrib and install,by the way,it seems that the prefix has bugs,I had to modify the Makefile and then install success. and then,I imported the prefix.sql.I did a test,but I found I can't create index of gist_prefix_range_ops,not found! I can insert the column prefix with type prefix_range. ps : doest it work in C local,or uft8? 2009/2/26 Harald Fuchs > In article <8f750b7c0902250259w6065515as350aca3b5d7d8173@mail.gmail.com>, > Tony Liao writes: > > > 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. > > If I understand you correctly, the "prefix" contrib package is what > you need: > > CREATE TABLE tableA ( > id serial NOT NULL, > prefix prefix_range NOT NULL, > PRIMARY KEY (id) > ); > > CREATE INDEX tableA_prefix_ix on tableA > USING gist (prefix gist_prefix_range_ops); > > COPY tableA (prefix) FROM stdin; > john > tom > anne > jim > \. > > INSERT INTO tableA (prefix) > SELECT x || 'test' > FROM generate_series (1, 10000) g(x); > > ANALYZE tableA; > > EXPLAIN ANALYZE > SELECT id, prefix > FROM tableA > WHERE prefix @> 'johnsmith'; > > will return something like that: > > Bitmap Heap Scan on tablea (cost=4.33..32.10 rows=10 width=19) (actual > time=0.035..0.036 rows=1 loops=1) > Recheck Cond: (prefix @> 'johnsmith[]'::prefix_range) > -> Bitmap Index Scan on tablea_prefix_ix (cost=0.00..4.33 rows=10 > width=0) (actual time=0.026..0.026 rows=1 loops=1) > Index Cond: (prefix @> 'johnsmith[]'::prefix_range) > Total runtime: 0.133 ms > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > --000e0cd28d5249f8eb0463cb90e0 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable thanks,everybody!
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Harald,you understand c= orrectly.I downloaded the prefix contrib and install,by the way,it seems th= at the prefix has bugs,I had to modify the Makefile and then install succes= s.
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 and then,I imported the prefix.sql.I d= id a test,but I found I can't create index of gist_prefix_range_ops,not= found! I can insert the column prefix with type prefix_range.
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ps : doest it work in C local,or uft8?
2009/2/26 Harald Fuchs <<= a href=3D"mailto:hari.fuchs@gmail.com">hari.fuchs@gmail.com><= br>
In article <8f750b7c0902250259w6065515as350aca3b5d7d8173@mail.gmail.= com>,
Tony Liao <tonyliao@yuehetone.com> writes:

> 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 "johnsmith"'= s prefix match table_A.prefix,so
> I do select id from table_A where 'johnsmith' like prefix||= 9;%'=A0 ,the table_A is
> very large so I would like to make index. create table_A_index on tabl= e_A
> (prefix)
> =A0 =A0 =A0 I try to explain analyze,but it doesn't work ,it use s= eq scan.
> =A0=A0=A0=A0=A0 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.

If I understand you correctly, the "prefix" contrib package= is what
you need:

=A0CREATE TABLE tableA (
=A0 =A0id serial NOT NULL,
=A0 =A0prefix prefix_range NOT NULL,
=A0 =A0PRIMARY KEY (id)
=A0);

=A0CREATE INDEX tableA_prefix_ix on tableA
=A0USING gist (prefix gist_prefix_range_ops);

=A0COPY tableA (prefix) FROM stdin;
=A0john
=A0tom
=A0anne
=A0jim
=A0\.

=A0INSERT INTO tableA (prefix)
=A0SELECT x || 'test'
=A0FROM generate_series (1, 10000) g(x);

=A0ANALYZE tableA;

=A0EXPLAIN ANALYZE
=A0SELECT id, prefix
=A0FROM tableA
=A0WHERE prefix @> 'johnsmith';

will return something like that:

=A0Bitmap Heap Scan on tablea =A0(cost=3D4.33..32.10 rows=3D10 width=3D19) = (actual time=3D0.035..0.036 rows=3D1 loops=3D1)
=A0 Recheck Cond: (prefix @> 'johnsmith[]'::prefix_range)
=A0 -> =A0Bitmap Index Scan on tablea_prefix_ix =A0(cost=3D0.00..4.33 r= ows=3D10 width=3D0) (actual time=3D0.026..0.026 rows=3D1 loops=3D1)
=A0 =A0 =A0 =A0 Index Cond: (prefix @> 'johnsmith[]'::prefix_ra= nge)
=A0Total runtime: 0.133 ms


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

--000e0cd28d5249f8eb0463cb90e0--