Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 624FE9FB204 for ; Tue, 6 Feb 2007 15:06:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 19762-05 for ; Tue, 6 Feb 2007 15:06:33 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.173]) by postgresql.org (Postfix) with ESMTP id E11279FB1E5 for ; Tue, 6 Feb 2007 15:06:34 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so1501929ugf for ; Tue, 06 Feb 2007 11:06:33 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=K4CkqYLPT9tLRUfMLRTTKbGK9MwwuOveWaanXte4QeD4bqhRYoowiR3hc7v2PViwMCy5+dj3xxizYlX/FyvJdWNAKQAly3uIsMbFIXEpFGqt/dj7LU+bqTmuf23fUR2voSF2q3jYEoMLpcEtCnx28mwVq6siv3NLOD4KC0P3YBc= Received: by 10.78.151.3 with SMTP id y3mr1866287hud.1170788793075; Tue, 06 Feb 2007 11:06:33 -0800 (PST) Received: by 10.78.45.3 with HTTP; Tue, 6 Feb 2007 11:06:32 -0800 (PST) Message-ID: Date: Tue, 6 Feb 2007 14:06:32 -0500 From: "Merlin Moncure" To: "Mark Lewis" Subject: Re: How long should it take to insert 200,000 records? Cc: "Scott Marlowe" , "Karen Hill" , pgsql-performance@postgresql.org In-Reply-To: <1170786686.16656.484.camel@archimedes> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <1170722158.973141.208230@j27g2000cwj.googlegroups.com> <1170778484.5451.97.camel@state.g2switchworks.com> <1170780953.5451.99.camel@state.g2switchworks.com> <1170786686.16656.484.camel@archimedes> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/72 X-Sequence-Number: 23126 On 2/6/07, Mark Lewis wrote: > > actually, I get the stupid award also because RI check to unindexed > > column is not possible :) (this haunts deletes, not inserts). > > Sure it's possible: > > CREATE TABLE parent (col1 int4); > -- insert many millions of rows into parent > CREATE TABLE child (col1 int4 REFERENCES parent(col1)); > -- insert many millions of rows into child, very very slowly. the database will not allow you to create a RI link out unless the parent table has a primary key/unique constraint, which the database backs with an index....and you can't even trick it afterwards by dropping the constraint. it's the other direction, when you cascade forwards when you can have a problem. this is most common with a delete, but can also happen on an update of a table's primary key with child tables referencing it. merlin