From: | "Marc G(dot) Fournier" <scrappy(at)postgresql(dot)org> |
---|---|
To: | Michael Fuhr <mike(at)fuhr(dot)org> |
Cc: | "Marc G(dot) Fournier" <scrappy(at)postgresql(dot)org>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: CHECK vs REFERENCES |
Date: | 2005-09-10 04:03:03 |
Message-ID: | 20050910010141.O1170@ganymede.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Browne | 2005-09-10 05:21:09 | Re: please comment on cpu 32 bit or 64 bit |
Previous Message | Michael Fuhr | 2005-09-10 03:49:07 | Re: CHECK vs REFERENCES |