2009/2/25 Michael Monnerie <michael.monne...@is.it-management.at> > On Mittwoch 25 Februar 2009 Tony Liao wrote: > > 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||'%' > > SELECT id FROM table_A WHERE prefix LIKE 'johnsmith%';
* in my table,the values of prefix are ('john','tom','anne','jim'......),so I don't think my SELECT was wrong.* by the way,my postgres-server version is 8.36.thanks > > > > ,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. > > Because your SELECT was wrong. > > > 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. > > thanks > > ps:I have another table table_B would use table_B.prefix= > > table_A.prefix.so how can I create the index? > > That's a foreign key, if I understand you correctly. > > ALTER TABLE ONLY B > ADD CONSTRAINT B_prefix_fkey FOREIGN KEY (prefix) REFERENCES > A(prefix) ON UPDATE CASCADE ON DELETE CASCADE; > > mfg zmi > -- > // Michael Monnerie, Ing.BSc ----- http://it-management.at > // Tel: 0660 / 415 65 31 .network.your.ideas. > // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" > // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 > // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4 > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >