pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCHECK vs REFERENCES
5+ messages / 3 participants
[nested] [flat]
* CHECK vs REFERENCES
@ 2005-09-10 03:23 Marc G. Fournier <scrappy@postgresql.org>
2005-09-10 03:49 ` Re: CHECK vs REFERENCES Michael Fuhr <mike@fuhr.org>
2005-09-21 15:35 ` Re: CHECK vs REFERENCES Vivek Khera <vivek@khera.org>
0 siblings, 2 replies; 5+ messages in thread
From: Marc G. Fournier @ 2005-09-10 03:23 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: CHECK vs REFERENCES
2005-09-10 03:23 CHECK vs REFERENCES Marc G. Fournier <scrappy@postgresql.org>
@ 2005-09-10 03:49 ` Michael Fuhr <mike@fuhr.org>
2005-09-10 04:03 ` Re: CHECK vs REFERENCES Marc G. Fournier <scrappy@postgresql.org>
1 sibling, 1 reply; 5+ messages in thread
From: Michael Fuhr @ 2005-09-10 03:49 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: CHECK vs REFERENCES
2005-09-10 03:23 CHECK vs REFERENCES Marc G. Fournier <scrappy@postgresql.org>
2005-09-10 03:49 ` Re: CHECK vs REFERENCES Michael Fuhr <mike@fuhr.org>
@ 2005-09-10 04:03 ` Marc G. Fournier <scrappy@postgresql.org>
2005-09-10 13:06 ` Re: CHECK vs REFERENCES Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 5+ messages in thread
From: Marc G. Fournier @ 2005-09-10 04:03 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Marc G. Fournier <scrappy@postgresql.org>; 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@hub.org Yahoo!: yscrappy ICQ: 7615664
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: CHECK vs REFERENCES
2005-09-10 03:23 CHECK vs REFERENCES Marc G. Fournier <scrappy@postgresql.org>
2005-09-10 03:49 ` Re: CHECK vs REFERENCES Michael Fuhr <mike@fuhr.org>
2005-09-10 04:03 ` Re: CHECK vs REFERENCES Marc G. Fournier <scrappy@postgresql.org>
@ 2005-09-10 13:06 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Fuhr @ 2005-09-10 13:06 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: CHECK vs REFERENCES
2005-09-10 03:23 CHECK vs REFERENCES Marc G. Fournier <scrappy@postgresql.org>
@ 2005-09-21 15:35 ` Vivek Khera <vivek@khera.org>
1 sibling, 0 replies; 5+ messages in thread
From: Vivek Khera @ 2005-09-21 15:35 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-09-21 15:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-10 03:23 CHECK vs REFERENCES Marc G. Fournier <scrappy@postgresql.org>
2005-09-10 03:49 ` Michael Fuhr <mike@fuhr.org>
2005-09-10 04:03 ` Marc G. Fournier <scrappy@postgresql.org>
2005-09-10 13:06 ` Michael Fuhr <mike@fuhr.org>
2005-09-21 15:35 ` Vivek Khera <vivek@khera.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox