Thread: CHECK vs REFERENCES
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 ... The case is where I just want to check that a value being inserted is one of a few possible values, with that list of values rarely (if ever) changing, so havng a 'flexible list' REFERENCED seems relatively overkill ... Thoughts, or pointers to a doc that disproves, or proves, what I believe? Thanks ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
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
On Fri, 9 Sep 2005, Michael Fuhr wrote: > 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 Yowch, I expected CHECK to be better ... but not so significantly ... I figured I'd be saving milliseconds, which, on a busy server, would add up fast ... but not 10k' of milliseconds ... Thanks, that definitely shows a major benefit ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On Sat, Sep 10, 2005 at 01:03:03AM -0300, Marc G. Fournier wrote: > On Fri, 9 Sep 2005, Michael Fuhr wrote: > >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 > > Yowch, I expected CHECK to be better ... but not so significantly ... I > figured I'd be saving milliseconds, which, on a busy server, would add up > fast ... but not 10k' of milliseconds ... Results will differ depending on the table structure: if you're indexing ten columns and have five triggers then the foreign key check will have less of an overall impact. -- Michael Fuhr
On Sep 9, 2005, at 11:23 PM, Marc G. Fournier wrote: > The case is where I just want to check that a value being inserted > is one of a few possible values, with that list of values rarely > (if ever) changing, so havng a 'flexible list' REFERENCED seems > relatively overkill ... > That's what I thought until the first time that list needed to be altered. At this point, it becomes a royal pain. point to take: do it right the first time, or you have to do it over, and over, and over... Vivek Khera, Ph.D. +1-301-869-4449 x806