Reece,
> Does anyone know what's going on here? I vaguely remember that
> postgresql keeps frequency estimates of words... is the problem that the
> new db doesn't have such estimates and therefore incorrectly guesses
> that an index is better?
What encoding does the new box have? Postgres is generally unable to use an
index on "begins with" queries unless the database has "C" encoding. One of
the differences between your old Red Hat and your new SuSE is that the
default shell encoding will have changed from "C" to "en_US". You may need
to re-initdb with --locale='C' .
--
Josh Berkus
Aglio Database Solutions
San Francisco