Обсуждение: Leading substrings - alternatives with 8.1.3?
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
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@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
On 5/1/06 12:47 PM, "Martijn van Oosterhout" <kleptog@svana.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
On Mon, May 01, 2006 at 01:45:35PM -0500, Wes wrote: > On 5/1/06 12:47 PM, "Martijn van Oosterhout" <kleptog@svana.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@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.