From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: pgsql-performance@postgresql.org
Subject: Re: Performance of DOMAINs
Date: Wed, 21 Jun 2006 18:19:07 -0700
User-Agent: KMail/1.8
Cc: David Wheeler <david@kineticode.com>
References: <54D833BE-A58F-47B8-B070-2AEDC0802468@kineticode.com>
In-Reply-To: <54D833BE-A58F-47B8-B070-2AEDC0802468@kineticode.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200606211819.08239.josh@agliodbs.com>

David,

> But I'm also interested in how Elein made the email domain case-
> insensitive, since I'd like to have/create a truly case-insensitive
> text type (ITEXT anyone?). The functions for the operator class there
> were mainly written in SQL, and if it adds a significant overhead,
> I'm not sure it'd be a good idea to use that approach for a case-
> insensitive text type, since I use it quite a lot in my apps, and
> often do LIKE queries against text data. Thoughts?

Well, current case-insensitivity hacks definitely aren't compatible with 
LIKE as far as "begins with" indexes are concerned.   Of course, floating 
LIKEs (%value%) are going to suck no matter what data type you're using.

I created an operator for CI equality ... =~ ... which performs well on 
indexed columns.   But it doesn't do "begins with".

ITEXT is a TODO, but there are reasons why it's harder than it looks.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco