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: [EMAIL PROTECTED]           Yahoo!: yscrappy              ICQ: 7615664

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org

Reply via email to