Обсуждение: 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