[PERFORM] Confusion about locales and 'like' indexes
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
Re: [PERFORM] Confusion about locales and 'like' indexes
Dan Harris [EMAIL PROTECTED] 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
