> 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