Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id D61F71337B5A
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  1 Dec 2010 12:34:16 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 67412-06
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  1 Dec 2010 16:34:09 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from lpo-relay-04.vmsinfo.com (lpo-relay-04.vmsinfo.com
	[216.169.137.115])
	by mail.postgresql.org (Postfix) with ESMTP id 0FBC3133740B
	for <pgsql-performance@postgresql.org>;
	Wed,  1 Dec 2010 12:34:08 -0400 (AST)
X-IronPort-AV: E=Sophos;i="4.59,284,1288584000"; d="scan'208,217";a="27293635"
Received: from unknown (HELO lpo-mailbox-01.vmsinfo.com) ([216.169.137.251])
	by lpo-relay-04.vmsinfo.com with ESMTP; 01 Dec 2010 11:34:05 -0500
Received: from [10.101.43.35] (unknown [216.169.135.235])
	by lpo-mailbox-01.vmsinfo.com (Postfix) with ESMTP id 57B3A7D00B;
	Wed,  1 Dec 2010 11:34:05 -0500 (EST)
Message-ID: <4CF678FC.9050003@vmsinfo.com>
Date: Wed, 01 Dec 2010 11:34:04 -0500
From: Mladen Gogala <mladen.gogala@vmsinfo.com>
Organization: VMS
User-Agent: Thunderbird 2.0.0.24 (X11/20101027)
MIME-Version: 1.0
To: Mario Splivalo <mario.splivalo@megafon.hr>
CC: "jd@commandprompt.com" <jd@commandprompt.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: SELECT INTO large FKyed table is slow
References: <4CF24103.8030209@megafon.hr>
	<1291168052.20631.1963.camel@jd-desktop>
	<4CF5FF0A.9040605@megafon.hr>
In-Reply-To: <4CF5FF0A.9040605@megafon.hr>
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.186 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, MIME_HTML_ONLY=0.723,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201012/8
X-Sequence-Number: 41460

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Mario Splivalo wrote:
<blockquote cite="mid:4CF5FF0A.9040605@megafon.hr" type="cite"><br>
  <pre wrap=""><!---->
Yes, as Mladen Gogala had advised. No noticable change in performance -
it's still slow :)
  </pre>
</blockquote>
<br>
Declaring constraints as deferrable  doesn't do anything as such, you
have to actually set the constraints deferred to have an effect. You
have to do it within a transaction block. If done outside of the
transaction block, there is no effect:<br>
<br>
This is what happens when "set constraints" is issued outside the
transaction block:<br>
<br>
<blockquote>&lt; constraint test1_pk primary key(col1)
deferrable);            <br>
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test1_pk" for table "test1"<br>
CREATE TABLE<br>
Time: 41.218 ms<br>
scott=# set constraints all deferred;                           <br>
SET CONSTRAINTS<br>
Time: 0.228 ms<br>
scott=# begin;                                      <br>
BEGIN<br>
Time: 0.188 ms<br>
scott=#  insert into test1 values(1);               <br>
INSERT 0 1<br>
Time: 0.929 ms<br>
scott=#  insert into test1 values(1);   <br>
ERROR:  duplicate key value violates unique constraint "test1_pk"<br>
DETAIL:  Key (col1)=(1) already exists.<br>
scott=# end;<br>
ROLLBACK<br>
Time: 0.267 ms<br>
scott=# <br>
  <br>
</blockquote>
It works like a charm when issued within the transaction block:<br>
<blockquote>scott=# begin;                          <br>
BEGIN<br>
Time: 0.202 ms<br>
scott=# set constraints all deferred;   <br>
SET CONSTRAINTS<br>
Time: 0.196 ms<br>
scott=#  insert into test1 values(1);   <br>
INSERT 0 1<br>
Time: 0.334 ms<br>
scott=#  insert into test1 values(1);   <br>
INSERT 0 1<br>
Time: 0.327 ms<br>
scott=# end;<br>
ERROR:  duplicate key value violates unique constraint "test1_pk"<br>
DETAIL:  Key (col1)=(1) already exists.<br>
scott=# <br>
</blockquote>
I was able to insert the same value twice, it only failed at the end of
the transaction.<br>
<blockquote cite="mid:4CF5FF0A.9040605@megafon.hr" type="cite">
  <pre wrap="">
But, just for the sake of clarification - I tought that DEFERRABLE would
matter if I do a lot of INSERTs, inside a FOR loop or something like
that. Since I'm doing INSERT INTO ... SELECT, does it makes any difference?
  </pre>
</blockquote>
You cannot tell which part takes a long time, select or insert, without
profiling. I certainly cannot do it over the internet.<br>
<br>
<pre class="moz-signature" cols="72">-- 
 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
<a class="moz-txt-link-freetext" href="http://www.vmsinfo.com">http://www.vmsinfo.com</a> 
The Leader in Integrated Media Intelligence Solutions


</pre>
</body>
</html>