pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Confusion about locales and 'like' indexes 2+ messages / 2 participants [nested] [flat]
* Confusion about locales and 'like' indexes @ 2005-03-02 00:44 Dan Harris <fbsd@drivefaster.net> 2005-03-02 01:42 ` Re: Confusion about locales and 'like' indexes Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: Dan Harris @ 2005-03-02 00:44 UTC (permalink / raw) To: pgsql-performance 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Confusion about locales and 'like' indexes 2005-03-02 00:44 Confusion about locales and 'like' indexes Dan Harris <fbsd@drivefaster.net> @ 2005-03-02 01:42 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2005-03-02 01:42 UTC (permalink / raw) To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-03-02 01:42 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-03-02 00:44 Confusion about locales and 'like' indexes Dan Harris <fbsd@drivefaster.net> 2005-03-02 01:42 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox