Re: "like" and index

hi Harald,
I reboot the machine and create index,it works.thanks.

2009/2/26 Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com>

> In article <8f750b7c0902250259w6065515as350aca3b5d7d8173(at)mail(dot)gmail(dot)com>,
> Tony Liao <tonyliao(at)yuehetone(dot)com> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>