(Straying a bit from the OP, but this is fun, no? And anyway, I rather expect this may be interesting/useful for him too).
An alternative (in postgresql) would be to use regex-index, which can be used when the submitted search-string or regex is anchored:
select count(*) from azjunk6; -- 1 million rows random data:
count
---------
1000000
(1 row)
-- without index:
select * from azjunk6 where txt ~ '^car[sz]';
txt
+
----------------------------------------------------------------------
+------------
carsxbutsvamedynximrftmimgtzirtuorik lunamb qpjvwmixlxpmcu mm rzotj
+jnfxr syfrj
carzfhndjznvpgcpwqb fp bqpljspqqpzfbbswefzs pjoocqztqkjxyvbr qalcfzme
+bezz ftmyi
carziicmi zzzvt beqsupgdwkhdg luvvmhhay bj b r soaiyfftiqgq hs brdzaf
+dztmtvfvrdn
carziogaizohcqcphs ksucyeod q yvfallob pctvmwplm igzsqalyy dqsjpiikx
+wyyxesenbeq
carzw rcfwlqcweao jzeyxkchgc g vyvujtbsbeiewj inuelmldsa mpjevzmo pc
+pwi kfajug
carzxrk qyk palimcwokbw hbdcsmxehcsnrop prrokygyi ssngegzksrzvged cu
+oxr yozt ca
(6 rows)
Time: 1147.420 ms
-- now make a text_pattern_ops index:
create index azjunk6_text_pattern_ops_idx on azjunk6 (txt text_patter
+n_ops);
Time: 7282.579 ms
-- with index:
select * from azjunk6 where txt ~ '^car[sz]';
txt
+
----------------------------------------------------------------------
+------------
carsxbutsvamedynximrftmimgtzirtuorik lunamb qpjvwmixlxpmcu mm rzotj
+jnfxr syfrj
carzfhndjznvpgcpwqb fp bqpljspqqpzfbbswefzs pjoocqztqkjxyvbr qalcfzme
+bezz ftmyi
carziicmi zzzvt beqsupgdwkhdg luvvmhhay bj b r soaiyfftiqgq hs brdzaf
+dztmtvfvrdn
carziogaizohcqcphs ksucyeod q yvfallob pctvmwplm igzsqalyy dqsjpiikx
+wyyxesenbeq
carzw rcfwlqcweao jzeyxkchgc g vyvujtbsbeiewj inuelmldsa mpjevzmo pc
+pwi kfajug
carzxrk qyk palimcwokbw hbdcsmxehcsnrop prrokygyi ssngegzksrzvged cu
+oxr yozt ca
(6 rows)
Time: 12.524 ms --> 100x faster
(It can be handy to have both a 'normal' btree index *and* such a text_pattern_ops regex index.)
See also: PostgreSQL index opclasses
You can get another interesting indextype from pg_trgm, a postgresql extension. This will give you not indexed regexen but indexed trigrams: PostgreSQL pg_trgm extension. (disadvantage: large index-size)
And FWIW: in 9.2devel, there is work ongoing to make it possible to combine the two: regexed trigram indexes... |