Re: Relative performance of prefix and suffix string matching

[Tore Halvorsen <tore.halvorsen@xxxxxxxxx>]

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