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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2011/12/14 Rural Hunter <ruralhunter@xxxxxxxxx>:
> 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@xxxxxxxxxxxxxx

Regards,
Marti

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux