Date: Wed, 21 Jun 2006 14:02:43 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: David Wheeler <david@kineticode.com>
Cc: PostgreSQL Performance List <pgsql-performance@postgresql.org>
Subject: Re: Performance of DOMAINs
Message-ID: <20060621190243.GE93655@pervasive.com>
References: <54D833BE-A58F-47B8-B070-2AEDC0802468@kineticode.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <54D833BE-A58F-47B8-B070-2AEDC0802468@kineticode.com>
User-Agent: Mutt/1.5.11

On Wed, Jun 21, 2006 at 11:26:16AM -0700, David Wheeler wrote:
> Howdy,
> 
> Didn't see anything in the archives, so I thought I'd ask: has anyone  
> done any work to gauge the performance penalty of using DOMAINs? I'm  
> thinking of something like Elein's email DOMAIN:
> 
>   http://www.varlena.com/GeneralBits/
> 
> I figured that most simple domains that have a constraint check are  
> no faster or slower than tables with constraints that validate a  
> particular column. Is that the case?
 
Probably. Only thing that might pose a difference is if you're doing a
lot of manipulating of the domain that didn't involve table access;
presumably PostgreSQL will perform the checks every time you cast
something to a domain.

> 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  

http://gborg.postgresql.org/project/citext/projdisplay.php

> 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?
> 
> Many TIA,
> 
> David
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461