Re: Index on substring

2011/6/5 Håvard Wahl Kongsgård <haavard(dot)kongsgaard(at)gmail(dot)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