Skip site navigation (1) Skip section navigation (2)

Re: CHECK vs REFERENCES

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: "Marc G(dot) Fournier" <scrappy(at)postgresql(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: CHECK vs REFERENCES
Date: 2005-09-10 03:49:07
Message-ID: 20050910034907.GA40382@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-performance
On Sat, Sep 10, 2005 at 12:23:19AM -0300, Marc G. Fournier wrote:
> Which is faster, where the list involved is fixed?  My thought is that 
> since it doesn't have to check a seperate table, the CHECK itself should 
> be the faster of the two, but I can't find anything that seems to validate 
> that theory ...

Why not just benchmark each method as you intend to use them?  Here's
a simplistic example:

CREATE TABLE test_none (
    val  integer NOT NULL
);

CREATE TABLE test_check (
    val  integer NOT NULL CHECK (val IN (1, 2, 3, 4, 5))
);

CREATE TABLE test_vals (
    id  integer PRIMARY KEY
);
INSERT INTO test_vals SELECT * FROM generate_series(1, 5);

CREATE TABLE test_fk (
    val  integer NOT NULL REFERENCES test_vals
);

\timing

INSERT INTO test_none SELECT 1 FROM generate_series(1, 100000);
INSERT 0 100000
Time: 3109.089 ms

INSERT INTO test_check SELECT 1 FROM generate_series(1, 100000);
INSERT 0 100000
Time: 3492.344 ms

INSERT INTO test_fk SELECT 1 FROM generate_series(1, 100000);
INSERT 0 100000
Time: 23578.853 ms

-- 
Michael Fuhr

In response to

Responses

pgsql-performance by date

Next:From: Marc G. FournierDate: 2005-09-10 04:03:03
Subject: Re: CHECK vs REFERENCES
Previous:From: Marc G. FournierDate: 2005-09-10 03:23:19
Subject: CHECK vs REFERENCES

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group