Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 63D396333DA for ; Thu, 26 Feb 2009 01:35:53 -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 57092-08 for ; Thu, 26 Feb 2009 01:35:44 -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.170]) by mail.postgresql.org (Postfix) with ESMTP id 6CBE263317C for ; Thu, 26 Feb 2009 01:35:44 -0400 (AST) Received: by wf-out-1314.google.com with SMTP id 23so423097wfg.28 for ; Wed, 25 Feb 2009 21:35:43 -0800 (PST) MIME-Version: 1.0 Received: by 10.142.132.2 with SMTP id f2mr465431wfd.261.1235626543255; Wed, 25 Feb 2009 21:35:43 -0800 (PST) In-Reply-To: References: <8f750b7c0902250259w6065515as350aca3b5d7d8173@mail.gmail.com> Date: Thu, 26 Feb 2009 13:35:43 +0800 Message-ID: <8f750b7c0902252135l17a6aac0v4f9afc8bbd5fe5df@mail.gmail.com> Subject: Re: "like" and index From: Tony Liao To: Harald Fuchs Cc: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd179c42f91080463cbb9e4 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/225 X-Sequence-Number: 31446 --000e0cd179c42f91080463cbb9e4 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit hi Harald, I reboot the machine and create index,it works.thanks. 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 > --000e0cd179c42f91080463cbb9e4 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable hi Harald,
=A0=A0=A0=A0=A0 I reboot the machine and create index,it work= s.thanks.

2009/2/26 Harald Fuchs <hari.fuchs@gmail.com<= /a>>
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

--000e0cd179c42f91080463cbb9e4--