On Fri, Sep 23, 2011 at 11:47 AM, Andrew Rose <andrew.r...@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