Re: Performance of DOMAINs

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461