Re: [PERFORM] CHECK vs REFERENCES
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] CHECK vs REFERENCES
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, 10); INSERT 0 10 Time: 3492.344 ms INSERT INTO test_fk SELECT 1 FROM generate_series(1, 10); INSERT 0 10 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] 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: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] CHECK vs REFERENCES
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, 10); INSERT 0 10 Time: 3109.089 ms INSERT INTO test_check SELECT 1 FROM generate_series(1, 10); INSERT 0 10 Time: 3492.344 ms INSERT INTO test_fk SELECT 1 FROM generate_series(1, 10); INSERT 0 10 Time: 23578.853 ms -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] CHECK vs REFERENCES
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, 10); INSERT 0 10 Time: 3109.089 ms INSERT INTO test_check SELECT 1 FROM generate_series(1, 10); INSERT 0 10 Time: 3492.344 ms INSERT INTO test_fk SELECT 1 FROM generate_series(1, 10); INSERT 0 10 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
