Greetings,

I have been beating myself up today trying to optimize indices for a
query that uses LIKE. In my research I have read that the locale
setting may affect PostgreSQL's choice of seq scan vs index scan. I am
running Fedora Core 2 and it appears when I run "locale" that it is set
to 'en.US-UTF-8'.

Did I fall into a "gotcha" trap here about C vs non-C locales? I'm not
much of a C programmer so I have no idea what all this touches and
everything has been left as default during PG compilation as well as
Fedora install. I can pg_dump and initdb again with --locale=C if
this will allow my LIKE queries to use indexes, but I just wanted to
know if there was some other place I needed to change locales in the
system? e.g. postgresql.conf or env vars? Or, would the initdb and
reload alone fix it?

I'm running 8.0.1 if that matters.

Thanks


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Dan Harris <fbsd@drivefaster.net> writes:
> query that uses LIKE. In my research I have read that the locale
> setting may affect PostgreSQL's choice of seq scan vs index scan.

Non-C-locale indexes can't support LIKE because the sort ordering
isn't necessarily right.

> I am running Fedora Core 2 and it appears when I run "locale" that it
> is set to 'en.US-UTF-8'.

This is not a definitive indication of the environment the database
sees, though. Try "show lc_collate".

> I can pg_dump and initdb again with --locale=C if
> this will allow my LIKE queries to use indexes, but I just wanted to
> know if there was some other place I needed to change locales in the
> system? e.g. postgresql.conf or env vars? Or, would the initdb and
> reload alone fix it?

That would do it. Alternatively you can create special-purpose indexes
with one of the xxx_pattern_ops operator classes to support LIKE.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings