Re: "like" and index

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 <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
>