Lists: | pgsql-general |
---|
From: | Wes <wespvp(at)syntegra(dot)com> |
---|---|
To: | pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Leading substrings - alternatives with 8.1.3? |
Date: | 2006-05-01 17:16:46 |
Message-ID: | C07BACAE.242CF%wespvp@syntegra.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Back in the 7.4 era, to make leading substrings be indexed, I had to set
locale=C. See thread:
<http://archives.postgresql.org/pgsql-general/2005-02/msg00159.php>
I couldn't find any updates to this issue in the archives since then.
With 8.1.3, the default locale for my system is en_US.UTF8. Leading
substring searches result in a sequential search instead of an indexed
search. Do I still have to initdb to locale=C, or is there a better option
now?
Wes
From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Wes <wespvp(at)syntegra(dot)com> |
Cc: | pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Leading substrings - alternatives with 8.1.3? |
Date: | 2006-05-01 17:47:21 |
Message-ID: | 20060501174721.GA27150@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Mon, May 01, 2006 at 12:16:46PM -0500, Wes wrote:
> Back in the 7.4 era, to make leading substrings be indexed, I had to set
> locale=C. See thread:
>
> <http://archives.postgresql.org/pgsql-general/2005-02/msg00159.php>
>
> I couldn't find any updates to this issue in the archives since then.
>
> With 8.1.3, the default locale for my system is en_US.UTF8. Leading
> substring searches result in a sequential search instead of an indexed
> search. Do I still have to initdb to locale=C, or is there a better option
> now?
Check the documentation, but if you declare a index with (for example)
text_pettern_ops, then LIKE will work even if the rest of your DB is
UTF-8.
Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
From: | Wes <wespvp(at)syntegra(dot)com> |
---|---|
To: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
Cc: | pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Leading substrings - alternatives with 8.1.3? |
Date: | 2006-05-01 18:45:35 |
Message-ID: | C07BC17F.242F4%wespvp@syntegra.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 5/1/06 12:47 PM, "Martijn van Oosterhout" <kleptog(at)svana(dot)org> wrote:
> Check the documentation, but if you declare a index with (for example)
> text_pettern_ops, then LIKE will work even if the rest of your DB is
> UTF-8.
My understanding of using operator classes is that I'd have to create two
indexes for each column - one with and one without the operator class. That
is also what was indicated in the original thread. Defining multiple
indexes on a given column isn't feasible, due to the database size (100
million rows per day).
Wes
From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Wes <wespvp(at)syntegra(dot)com> |
Cc: | pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Leading substrings - alternatives with 8.1.3? |
Date: | 2006-05-01 21:05:28 |
Message-ID: | 20060501210528.GC27150@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Mon, May 01, 2006 at 01:45:35PM -0500, Wes wrote:
> On 5/1/06 12:47 PM, "Martijn van Oosterhout" <kleptog(at)svana(dot)org> wrote:
>
> > Check the documentation, but if you declare a index with (for example)
> > text_pettern_ops, then LIKE will work even if the rest of your DB is
> > UTF-8.
>
> My understanding of using operator classes is that I'd have to create two
> indexes for each column - one with and one without the operator class. That
> is also what was indicated in the original thread. Defining multiple
> indexes on a given column isn't feasible, due to the database size (100
> million rows per day).
Oh right. If you want ordinary <,=,> to work for utf-8 and be indexed,
you need to have both. OTOH, if you're happy with text_pattern_ops for
ordinary lookups, you can use ~<~, ~=~, ~>~ to use the that index for
matches..
Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.