pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance of DOMAINs
8+ messages / 5 participants
[nested] [flat]
* Performance of DOMAINs
@ 2006-06-21 18:26 David Wheeler <david@kineticode.com>
2006-06-21 19:02 ` Re: Performance of DOMAINs Jim C. Nasby <jnasby@pervasive.com>
2006-06-21 20:08 ` Re: Performance of DOMAINs Tom Lane <tgl@sss.pgh.pa.us>
2006-06-22 01:19 ` Re: Performance of DOMAINs Josh Berkus <josh@agliodbs.com>
0 siblings, 3 replies; 8+ messages in thread
From: David Wheeler @ 2006-06-21 18:26 UTC (permalink / raw)
To: pgsql-performance
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?
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?
Many TIA,
David
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance of DOMAINs
2006-06-21 18:26 Performance of DOMAINs David Wheeler <david@kineticode.com>
@ 2006-06-21 19:02 ` Jim C. Nasby <jnasby@pervasive.com>
2 siblings, 0 replies; 8+ messages in thread
From: Jim C. Nasby @ 2006-06-21 19:02 UTC (permalink / raw)
To: David Wheeler <david@kineticode.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance of DOMAINs
2006-06-21 18:26 Performance of DOMAINs David Wheeler <david@kineticode.com>
@ 2006-06-21 20:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-22 18:11 ` Re: Performance of DOMAINs David Wheeler <david@kineticode.com>
2 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2006-06-21 20:08 UTC (permalink / raw)
To: David Wheeler <david@kineticode.com>; +Cc: pgsql-performance
David Wheeler <david@kineticode.com> writes:
> 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?
There are some reports in the archives of particular usage patterns
where they pretty much suck, because GetDomainConstraints() searches
pg_constraint every time it's called. We do what we can to avoid
calling that multiple times per query, but for something like a simple
INSERT ... VALUES into a domain column, the setup overhead is still bad.
I've been intending to try to fix things so that the search result can
be cached by typcache.c, but not gotten round to it. (The hard part,
if anyone wants to tackle it, is figuring out a way to clear the cache
entry when needed.)
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance of DOMAINs
2006-06-21 18:26 Performance of DOMAINs David Wheeler <david@kineticode.com>
2006-06-21 20:08 ` Re: Performance of DOMAINs Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-06-22 18:11 ` David Wheeler <david@kineticode.com>
0 siblings, 0 replies; 8+ messages in thread
From: David Wheeler @ 2006-06-22 18:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Jun 21, 2006, at 13:08, Tom Lane wrote:
> There are some reports in the archives of particular usage patterns
> where they pretty much suck, because GetDomainConstraints() searches
> pg_constraint every time it's called. We do what we can to avoid
> calling that multiple times per query, but for something like a simple
> INSERT ... VALUES into a domain column, the setup overhead is still
> bad.
I assume that there's no domain thingy that you already have that
could cache it, eh?
Sorry, I ask this as someone who knows no C and less about
PostgreSQL's internals.
Best,
David
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance of DOMAINs
2006-06-21 18:26 Performance of DOMAINs David Wheeler <david@kineticode.com>
@ 2006-06-22 01:19 ` Josh Berkus <josh@agliodbs.com>
2006-06-22 02:24 ` Re: Performance of DOMAINs Michael Glaesemann <grzm@seespotcode.net>
2006-06-22 18:18 ` Re: Performance of DOMAINs David Wheeler <david@kineticode.com>
2 siblings, 2 replies; 8+ messages in thread
From: Josh Berkus @ 2006-06-22 01:19 UTC (permalink / raw)
To: pgsql-performance; +Cc: David Wheeler <david@kineticode.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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance of DOMAINs
2006-06-21 18:26 Performance of DOMAINs David Wheeler <david@kineticode.com>
2006-06-22 01:19 ` Re: Performance of DOMAINs Josh Berkus <josh@agliodbs.com>
@ 2006-06-22 02:24 ` Michael Glaesemann <grzm@seespotcode.net>
2006-06-22 18:18 ` Re: Performance of DOMAINs David Wheeler <david@kineticode.com>
1 sibling, 1 reply; 8+ messages in thread
From: Michael Glaesemann @ 2006-06-22 02:24 UTC (permalink / raw)
To: ; +Cc: pgsql-performance@postgresql.org, David Wheeler <david@kineticode.com>
> since I'd like to have/create a truly case-insensitive
> text type (ITEXT anyone?).
I haven't seen it mentioned in this thread yet, but have you looked
at citext?
http://gborg.postgresql.org/project/citext/projdisplay.php
I don't have any experience with it, but perhaps it can do what
you're looking for.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance of DOMAINs
2006-06-21 18:26 Performance of DOMAINs David Wheeler <david@kineticode.com>
2006-06-22 01:19 ` Re: Performance of DOMAINs Josh Berkus <josh@agliodbs.com>
2006-06-22 02:24 ` Re: Performance of DOMAINs Michael Glaesemann <grzm@seespotcode.net>
@ 2006-06-22 18:18 ` David Wheeler <david@kineticode.com>
0 siblings, 0 replies; 8+ messages in thread
From: David Wheeler @ 2006-06-22 18:18 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: pgsql-performance
On Jun 21, 2006, at 19:24, Michael Glaesemann wrote:
> I haven't seen it mentioned in this thread yet, but have you looked
> at citext?
>
> http://gborg.postgresql.org/project/citext/projdisplay.php
>
> I don't have any experience with it, but perhaps it can do what
> you're looking for.
Yes, I've seen it. I haven't tried it, either. It'd be nice if it had
a compatible license with PostgreSQL, though.
Best,
David
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance of DOMAINs
2006-06-21 18:26 Performance of DOMAINs David Wheeler <david@kineticode.com>
2006-06-22 01:19 ` Re: Performance of DOMAINs Josh Berkus <josh@agliodbs.com>
@ 2006-06-22 18:18 ` David Wheeler <david@kineticode.com>
1 sibling, 0 replies; 8+ messages in thread
From: David Wheeler @ 2006-06-22 18:18 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance
On Jun 21, 2006, at 18:19, Josh Berkus wrote:
> Well, current case-insensitivity hacks definitely aren't compatible
> with
> LIKE as far as "begins with" indexes are concerned.
Yes, currently I use LOWER() for my indexes and for all LIKE, =, etc.
queries. This works well, but ORDER by of course isn't what I'd like.
That's one of the things that Elein's email domain addresses, albeit
with a USING keyword, which is unfortunate.
> Of course, floating
> LIKEs (%value%) are going to suck no matter what data type you're
> using.
Yes, I know that. :-) I avoid that.
> I created an operator for CI equality ... =~ ... which performs
> well on
> indexed columns. But it doesn't do "begins with".
Oops. So how could it perform well on indexed columns?
> ITEXT is a TODO, but there are reasons why it's harder than it looks.
I'm sure. I should bug potential future SoC students about it. ;-)
Best,
David
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-06-22 18:18 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-21 18:26 Performance of DOMAINs David Wheeler <david@kineticode.com>
2006-06-21 19:02 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-21 20:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-22 18:11 ` David Wheeler <david@kineticode.com>
2006-06-22 01:19 ` Josh Berkus <josh@agliodbs.com>
2006-06-22 02:24 ` Michael Glaesemann <grzm@seespotcode.net>
2006-06-22 18:18 ` David Wheeler <david@kineticode.com>
2006-06-22 18:18 ` David Wheeler <david@kineticode.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox