pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marc G. Fournier <scrappy@postgresql.org>
To: Michael Fuhr <mike@fuhr.org>
Cc: Marc G. Fournier <scrappy@postgresql.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: CHECK vs REFERENCES
Date: Sat, 10 Sep 2005 01:03:03 -0300 (ADT)
Message-ID: <20050910010141.O1170@ganymede.hub.org> (raw)
In-Reply-To: <20050910034907.GA40382@winnie.fuhr.org>
References: <20050910001821.E1170@ganymede.hub.org>
	<20050910034907.GA40382@winnie.fuhr.org>

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



view thread (5+ messages)  latest in thread

Message-ID: <20050910010141.O1170@ganymede.hub.org>
Permalink:  ../20050910010141.O1170@ganymede.hub.org/
Also on:    postgresql.org/message-id/20050910010141.O1170@ganymede.hub.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: scrappy@postgresql.org, mike@fuhr.org
  Subject: Re: CHECK vs REFERENCES
  In-Reply-To: <20050910010141.O1170@ganymede.hub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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