X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0F3C7D864A; Sat, 10 Sep 2005 01:03:13 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 51115-08; Sat, 10 Sep 2005 04:03:10 +0000 (GMT) Received: from hub.org (hub.org [200.46.204.220]) by svr1.postgresql.org (Postfix) with ESMTP id 0C29BD8632; Sat, 10 Sep 2005 01:03:11 -0300 (ADT) Received: from localhost (av.hub.org [200.46.204.144]) by hub.org (Postfix) with ESMTP id 71FD264B9FC; Sat, 10 Sep 2005 01:03:11 -0300 (ADT) Received: from hub.org ([200.46.204.220]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 55727-03; Sat, 10 Sep 2005 04:03:05 +0000 (GMT) Received: from ganymede.hub.org (blk-222-82-85.eastlink.ca [24.222.82.85]) by hub.org (Postfix) with ESMTP id DE9D164B906; Sat, 10 Sep 2005 01:03:05 -0300 (ADT) Received: by ganymede.hub.org (Postfix, from userid 1000) id 0DEBD368B9; Sat, 10 Sep 2005 01:03:04 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 07693353E2; Sat, 10 Sep 2005 01:03:04 -0300 (ADT) Date: Sat, 10 Sep 2005 01:03:03 -0300 (ADT) From: "Marc G. Fournier" X-X-Sender: scrappy@ganymede.hub.org To: Michael Fuhr Cc: "Marc G. Fournier" , pgsql-performance@postgresql.org Subject: Re: CHECK vs REFERENCES In-Reply-To: <20050910034907.GA40382@winnie.fuhr.org> Message-ID: <20050910010141.O1170@ganymede.hub.org> References: <20050910001821.E1170@ganymede.hub.org> <20050910034907.GA40382@winnie.fuhr.org> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.046 required=5 tests=[AWL=-0.004, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200509/121 X-Sequence-Number: 14429 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