"like" and index

From: Tony Liao <tonyliao(at)yuehetone(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: "like" and index
Date: 2009-02-25 10:59:27
Message-ID: 8f750b7c0902250259w6065515as350aca3b5d7d8173@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.
thanks
ps:I have another table table_B would use table_B.prefix=
table_A.prefix.so how can I create the index?

yours, Tony

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Michael Monnerie 2009-02-25 11:41:40 Re: "like" and index
Previous Message Lentes, Bernd 2009-02-25 10:05:55 Question to transaction ID wraparound