Re: operator exclusion constraints

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: operator exclusion constraints
Date: 2009-11-01 18:53:20
Message-ID: 1257101600.27737.159.camel@jdavis
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

New patch attached. Again, both attached patches are the same, but one
is the original patch from "git diff", compressed; and the other is the
same patch passed through filterdiff to make it a context diff. Also
available from my git repo (branch "operator-exclusion-constraints"):

http://git.postgresql.org/gitweb?p=users/jdavis/postgres.git;a=shortlog;h=refs/heads/operator-exclusion-constraints

All open issues are closed; ready for review. Comments welcome.

Completed:

* pg_dump support
* psql support
* clean up after brief review

Simple Performance Tests:

I used Dean Rasheed's python script from this email:

http://archives.postgresql.org/pgsql-hackers/2009-10/msg01527.php

modifying it slightly for the various combinations listed below.

All tests with 10 threads and 1000 loops. The constraint is always
uniqueness (with the operator exclusion constraints that means "CHECK
WITH ="). All results are the approximate average of 3 consecutive runs.
All of these are high-contention tests because they are all competing
over 100 unique values.

[ "btree" means the existing constraint enforcement mechanism in btree,
although the exclusion constraints use a btree as well in this test ]

btree/immediate: 14s
exclusion/immediate: 90s

btree/deferrable: 155s
exclusion/deferrable: 150s

The next tests I did were simple no-contention insert tests of 1M
integer values. I only tested with immediate.

btree/immediate: 15s
exclusion/immediate: 19s

So, the performance is marginally worse for no-contention, about the
same for high-contention deferrable, and significantly worse for
high-contention immediate.

I think that the performance for high-contention immediate could be
improved by being a little more clever to avoid restarting the index
scan on every conflict. However, I've already made a mistake in that
area (as Dean pointed out), so I'll leave it simple for now.

Right as the November commitfest starts, I'll be in Japan for about a
week, so I may be slow to respond to comments during that time.

Regards,
Jeff Davis

Attachment Content-Type Size
operator-exclusion-constraints-20091101.context.patch text/x-patch 101.0 KB
operator-exclusion-constraints-20091101.patch.gz application/x-gzip 24.6 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Simon Riggs 2009-11-01 22:42:15 Re: operator exclusion constraints
Previous Message Brendan Jurd 2009-11-01 18:35:11 Re: \du quite ugly in 8.4