Re: Is it possible to use index on column for regexp match operator '~'?

actually I stored the pattern in col1. I want to get the row whose col1
pattern matches one string 'aaa'.

于2011年12月15日 4:43:37,Marti Raudsepp写到:
> 2011/12/14 Rural Hunter<ruralhunter(at)gmail(dot)com>:
>> for example, the where condition is: where 'aaaa' ~ col1. I created a normal
>> index on col1 but seems it is not used.
>
> I assume you want to search values that match one particular pattern,
> that would be col1 ~ 'aaaa'
>
> The answer is, only very simple patterns that start with '^'. Note
> that you MUST use the text_pattern_ops index opclass:
>
> # create table words (word text);
> # copy words from '/usr/share/dict/words';
> # create index on words (word text_pattern_ops);
> # explain select * from words where word ~ '^post';
> Index Scan using words_word_idx on words (cost=0.00..8.28 rows=10 width=9)
> Index Cond: ((word ~>=~ 'post'::text) AND (word ~<~ 'posu'::text))
> Filter: (word ~ '^post'::text)
>
> ----
>
> If you just want to search for arbitrary strings, in PostgreSQL 9.1+
> you can use pg_trgm extension with a LIKE expression:
>
> # create extension pg_trgm;
> # create index on words using gist (word gist_trgm_ops);
> # explain select * from words where word like '%post%';
> Bitmap Heap Scan on words (cost=4.36..40.23 rows=10 width=9)
> Recheck Cond: (word ~~ '%post%'::text)
> -> Bitmap Index Scan on words_word_idx1 (cost=0.00..4.36 rows=10 width=0)
> Index Cond: (word ~~ '%post%'::text)
>
> ----
>
> There's also the "wildspeed" external module which is somewhat faster
> at this: http://www.sai.msu.su/~megera/wiki/wildspeed
>
> And someone is working to get pg_trgm support for arbitrary regular
> expression searches. This *may* become part of the next major
> PostgreSQL release (9.2)
> http://archives.postgresql.org/message-id/CAPpHfduD6EGNise5codBz0KcdDahp7--MhFz_JDD_FRPC7-i=A@mail.gmail.com
>
> Regards,
> Marti
>