Re: operator exclusion constraints [was: generalized index constraints]

On Sun, 2009-09-20 at 13:49 -0400, Tom Lane wrote:
> I'd vote for only supporting the former.

Ok.

I just did some brief non-scientific in-memory benchmarks. I think it
has promise, but for now I think it can safely be set aside.

Results appended.

> What worries me more about that syntax is the postfix-operator ambiguity
> --- I think it'll be hard to expand it to expressions. It might be
> better to put the operator at the front; or maybe you need an extra
> keyword in there.

How about "OPERATOR", like:

CONSTRAINT <name>
EXCLUSION (<expr> OPERATOR <op>, ...)
USING <method>;

I like it because it connects back to the name "operator exclusion
constraint".

Regards,
Jeff Davis

---------------------------
Results (oversimplified benchmark):

As a control, two unique btrees (using old uniqueness mechanism) takes
37s.

DDL (old syntax, haven't changed it yet):

create table one(a int, b int, c int);
create index one_a_b_c_idx on one(a,b,c);
alter table one add constraint one_a_b_constr
exclusion (a =, b =) using one_a_b_c_idx;
alter table one add constraint one_a_c_constr
exclusion (a =, c =) index one_a_b_c_idx;

create table two(a int, b int, c int);
create index two_a_b_idx on two(a,b);
create index two_a_c_idx on two(a,c);
alter table two add constraint two_a_c_constr
exclusion (a =, c =) index two_a_c_idx;
alter table two add constraint two_a_b_constr
exclusion (a =, b =) index two_a_b_idx;

Tests are of the form:

-- test inserting into table with one big index with 10 "b"
-- values per "a" value
insert into one select g1, g2, g2
from generate_series(1,100000) g1, generate_series(1,10) g2;

n: number of "a" values per "b" value
t1: results for one-index solution
t2: results for two-index solution

n t1 t2
-------+------+-------
1000 | 105s | 57s
100 | 47s | 54s
10 | 44s | 53s
1 | 42s | 56s

So, the one-index solution shows about 10-20% benefit over the two-index
solution when the number of "b" values per "a" value drops to around
100. Not bad, but nothing to write home about, because it's still
outperformed by the existing btree enforcement mechinism. I think it has
promise for some situations though; such as larger key size, leaf pages
not in memory, etc.