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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
