On Wed, Nov 22, 2006 at 11:24:33AM -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > On Wed, 22 Nov 2006, Alexander Presber wrote:
> >> CREATE INDEX idx_main_subject ON pdb.main (lower(main_subject::text)
> >> using varchar_ops);
>
> > IIRC, unless you're in C locale, you'll want varchar_pattern_ops rather
> > than varchar_ops on the index to make it considered for a LIKE search.
>
> text_pattern_ops would be better, seeing that the output of lower() is
> text not varchar. I'm a bit surprised the planner is able to make use
> of this index at all.
Since the original poster Alex is a colleage of mine and just ran out
the door, let me pass on his big THANK YOU on his behalf. He is all
smiles now, and the query is fast now.
He should also be wearing that ole' brown paper bag, since we even
have an inhouse wiki page documenting the need for varchar_pattern_ops :-)
Anyway, thanks a bunch everybody!
regards,
Alex & bkw