Re: "like" and index

[Tony Liao <tonyliao@xxxxxxxxxxxxx>]

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.fuchs@xxxxxxxxx>

In article <8f750b7c0902250259w6065515as350aca3b5d7d8173@xxxxxxxxxxxxxx>,

Tony Liao <tonyliao@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin