Reece,
> When md5() is explicitly cast to bpchar, the index is used. I ran
> across this issue long ago (in 7.x), but I thought that casting was now
> part of query optimization and that explicit casting was no longer
> required. I was wrong.
Well, the planner tries to figure out the casting. Sometimes it fails.
CHAR is particularly problematic in this regard; it's not exactly
equivalent to TEXT so the plannner can fail to match it up.
Your solution of course, is to create a shell function for md5 which
converts it to CHAR. Or just cast.
--Josh Berkus