LIKE optimization and locale

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: LIKE optimization and locale
Date: 2000-11-26 06:12:56
Message-ID: 200011260612.BAA11135@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

When locale is enabled, we have always had a problem using an index
with:

col LIKE 'abc%'

We need to make this:

col LIKE 'abc%' AND
col >= "abc" AND
col < "abd"

but when locale is enabled, we can't be sure what letter is greater than
'c' in this case.

Why don't we just spin through all 255 locale values, and find the
lowest value that is greater than comparison target. It takes only 255
comparisons, which is certainly faster than not using an index with
LIKE.

It is so simple, I don't know why I didn't think of it before. If
performance is a problem, we can do it once in the backend or even in
the postmaster and keep the collation ordering in a 256-byte array. We
may even be able to handle multi-byte in this way, though with 256^2, we
would need to do it once on postmaster startup.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alain Toussaint 2000-11-26 07:50:36 Re: Re: [NOVICE] Re: re : PHP and persistent connections
Previous Message Mitch Vincent 2000-11-26 06:00:27 Re: Re: [NOVICE] Re: re : PHP and persistent connections