Index on substring

2011/6/5 Håvard Wahl Kongsgård <haavard.kongsgaard@gmail.com>

Hi, my database performance badly on substring comparison between two very
large tables.
In postgresql 8.4 is it possible to create a index on a substring, or must
I create an new field for the substring match ( and then create a new index
for that field)?

--
Håvard Wahl Kongsgård

http://havard.security-review.net/

Hi,
I've checked that for 9.0, however for 8.4 should work as well:

CREATE INDEX i_test_sbstr ON test( substring(t, 5, 3) );

It is important that the substring() parameters in the index definition are
the same as you use in your query.

regards
Szymon