Re: Relative performance of prefix and suffix string matching

On Fri, Sep 23, 2011 at 11:47 AM, Andrew Rose <andrew(dot)rose(at)metaswitch(dot)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