From: Rural Hunter <ruralhunter@gmail.com>
To: Marti Raudsepp <marti@juffo.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Is it possible to use index on column for regexp match operator '~'?
Date: Thu, 15 Dec 2011 09:54:06 +0800
Message-ID: <4EE9533E.1050001@gmail.com> (raw)
In-Reply-To: <CABRT9RC==HByCGLRL3xHz05ng=19axvm0kEXLAGqgdHXJjjjpg@mail.gmail.com>
References: <4EE8C94B.5000607@gmail.com>
<CABRT9RC==HByCGLRL3xHz05ng=19axvm0kEXLAGqgdHXJjjjpg@mail.gmail.com>
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@gmail.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.g...
>
> Regards,
> Marti
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ruralhunter@gmail.com, marti@juffo.org
Subject: Re: Is it possible to use index on column for regexp match operator '~'?
In-Reply-To: <4EE9533E.1050001@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox