On Fri, Sep 23, 2011 at 11:47 AM, Andrew Rose
<andrew.rose@metaswitch.com> wrote:
Basic Question: In text fields, is prefix matching significantly faster than suffix matching?
If you are using text_pattern_ops, then yes.
2. Alternatively, I could store column 'rev_str' as a reversed version of column 'str' and have the client produce a reversed version of x on each query (call it r). Then the client would issue...
... or use an index on the reversed string.
create table foo (text text not null);
insert into foo select md5(generate_series(1, 1000000, 1)::text);
create index on foo(text text_pattern_ops);
create index on foo(reverse(text) text_pattern_ops);
explain select * from foo where text like 'f000' || '%' or reverse(text) like reverse('f000') || '%'
Bitmap Heap Scan on foo (cost=9.20..13.22 rows=200 width=33)
Recheck Cond: ((text ~~ 'f000%'::text) OR (reverse(text) ~~ '000f%'::text))
Filter: ((text ~~ 'f000%'::text) OR (reverse(text) ~~ '000f%'::text))
-> BitmapOr (cost=9.20..9.20 rows=1 width=0)
-> Bitmap Index Scan on foo_text_idx (cost=0.00..4.55 rows=1 width=0)
Index Cond: ((text ~>=~ 'f000'::text) AND (text ~<~ 'f001'::text))
-> Bitmap Index Scan on foo_reverse_idx (cost=0.00..4.55 rows=1 width=0)
Index Cond: ((reverse(text) ~>=~ '000f'::text) AND (reverse(text) ~<~ '000g'::text))
... at least this works for me :)
--
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
<demo> 2011 Tore Halvorsen || +052 0553034554
