Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group