Received: from cyclone.stortek.com (cyclone.stortek.com [129.80.22.130]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f29KxVx22448 for ; Fri, 9 Mar 2001 15:59:31 -0500 (EST) (envelope-from CreagRS@LOUISVILLE.STORTEK.COM) Received: from cyclone.stortek.com (localhost [127.0.0.1]) by cyclone.stortek.com (8.9.3/8.9.3) with ESMTP id NAA15095 for ; Fri, 9 Mar 2001 13:59:29 -0700 (MST) Received: from vinson-atm6.stortek.com (root@vinson.stortek.com [129.80.40.134] (may be forged)) by cyclone.stortek.com (8.9.3/8.9.3) with ESMTP id NAA15050; Fri, 9 Mar 2001 13:59:24 -0700 (MST) Received: from lsv-bridge.stortek.com (lsv-bridge.stortek.com [129.80.5.131]) by vinson-atm6.stortek.com (8.9.3/8.9.3) with ESMTP id NAA10915; Fri, 9 Mar 2001 13:59:23 -0700 (MST) Received: by lsv-bridge.stortek.com with Internet Mail Service (5.5.2653.19) id ; Fri, 9 Mar 2001 13:59:23 -0700 Message-ID: <10FE17AD5F7ED31188CE002048406DE8514CED@lsv-msg06.stortek.com> From: "Creager, Robert S" To: "'Stephan Szabo'" , Josh Berkus Cc: sqllist Subject: RE: cannot get CREATE TABLE AS to work Date: Fri, 9 Mar 2001 13:59:22 -0700 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: text/plain; charset="iso-8859-1" X-Archive-Number: 200103/202 X-Sequence-Number: 1856 Well, that explains why I wasn't seeing any appreciable speed increase with the INITIALLY DEFERRED. I tried mucking in pg_class, and saw a 3 fold increase in insert speed on inserts into my table with 2 relational triggers. SET CONSTRAINTS ALL DEFERRED does nothing to very little to increase the insertion speed. 15min 'INITIALLY DEFERRED' vs 13min 'ALL DEFERRED' vs 5min 'pg_class update'. And that 15 vs 13 could be machine activity. I'll appologize to anyone trying to follow this one sided converstion, as I wasn't paying attention to the e-mail headers and though Josh was replying to me only. Cheers, Rob > -----Original Message----- > > Well, you can always add deferrable initially immediate to > the constraint > and run a SET CONSTRAINTS ALL DEFERRED, but I'm not sure > that'll actually > be much faster, it still does a check per row I believe. > > It's hacky, but I'd say, if you don't have other triggers you > care about, > twiddle pg_class.reltriggers for the class to 0, do the insert, set it > back to what it was before and then run selects to make sure > the data is > valid (ie, would the constraint have failed). > > [ > assuming one column, something like: > > select * from fktable where not exists > (select * from pktable where pktable.pkcol=fktable.fkcol); > ] >