pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIs it possible to use index on column for regexp match operator '~'?
3+ messages / 2 participants
[nested] [flat]
* Is it possible to use index on column for regexp match operator '~'?
@ 2011-12-14 16:05 Rural Hunter <ruralhunter@gmail.com>
2011-12-14 20:43 ` Re: Is it possible to use index on column for regexp match operator '~'? Marti Raudsepp <marti@juffo.org>
0 siblings, 1 reply; 3+ messages in thread
From: Rural Hunter @ 2011-12-14 16:05 UTC (permalink / raw)
To: pgsql-performance
for example, the where condition is: where 'aaaa' ~ col1. I created a
normal index on col1 but seems it is not used.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Is it possible to use index on column for regexp match operator '~'?
2011-12-14 16:05 Is it possible to use index on column for regexp match operator '~'? Rural Hunter <ruralhunter@gmail.com>
@ 2011-12-14 20:43 ` Marti Raudsepp <marti@juffo.org>
2011-12-15 01:54 ` Re: Is it possible to use index on column for regexp match operator '~'? Rural Hunter <ruralhunter@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Marti Raudsepp @ 2011-12-14 20:43 UTC (permalink / raw)
To: Rural Hunter <ruralhunter@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Is it possible to use index on column for regexp match operator '~'?
2011-12-14 16:05 Is it possible to use index on column for regexp match operator '~'? Rural Hunter <ruralhunter@gmail.com>
2011-12-14 20:43 ` Re: Is it possible to use index on column for regexp match operator '~'? Marti Raudsepp <marti@juffo.org>
@ 2011-12-15 01:54 ` Rural Hunter <ruralhunter@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Rural Hunter @ 2011-12-15 01:54 UTC (permalink / raw)
To: Marti Raudsepp <marti@juffo.org>; +Cc: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-12-15 01:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-12-14 16:05 Is it possible to use index on column for regexp match operator '~'? Rural Hunter <ruralhunter@gmail.com>
2011-12-14 20:43 ` Marti Raudsepp <marti@juffo.org>
2011-12-15 01:54 ` Rural Hunter <ruralhunter@gmail.com>
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