From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Dan Harris <fbsd(at)drivefaster(dot)net> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Confusion about locales and 'like' indexes |
Date: | 2005-03-02 01:42:14 |
Message-ID: | 3457.1109727734@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Dan Harris <fbsd(at)drivefaster(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Alan Stange | 2005-03-02 02:28:36 | Re: multi billion row tables: possible or insane? |
Previous Message | Josh Berkus | 2005-03-02 01:11:18 | Re: multi billion row tables: possible or insane? |