Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id ABD501337B7F for ; Wed, 1 Dec 2010 13:00:37 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 59206-02 for ; Wed, 1 Dec 2010 17:00:30 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from out.mail.amis.hr (out1.mail.amis.hr [188.129.0.4]) by mail.postgresql.org (Postfix) with ESMTP id 49B791335B36 for ; Wed, 1 Dec 2010 13:00:29 -0400 (AST) Received: from out.fsec.sm.srv.amis.hr (out.fsec.sm.srv.amis.hr [192.168.106.22]) by out.mail.amis.hr (Postfix) with ESMTP id 0C805202AA; Wed, 1 Dec 2010 18:00:28 +0100 (CET) Received: from pps.filterd (out [127.0.0.1]) by out.fsec.sm.srv.amis.hr (8.14.3/8.14.3) with SMTP id oB1GvQnV010039; Wed, 1 Dec 2010 18:00:27 +0100 Received: from smtp.amis.hr (smtp.sm.srv.amis.hr [192.168.106.39]) by out.fsec.sm.srv.amis.hr with ESMTP id swn6vrag0-1; Wed, 01 Dec 2010 18:00:27 +0100 A-Original-Client-UID: 188.129.73.45 splivalo Received: from [192.168.10.1] (cpe-188-129-73-45.dynamic.amis.hr [188.129.73.45]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: splivalo) by smtp.amis.hr (Postfix) with ESMTPSA id 31E36287; Wed, 1 Dec 2010 18:00:27 +0100 (CET) Message-ID: <4CF67F2A.4070703@megafon.hr> Date: Wed, 01 Dec 2010 18:00:26 +0100 From: Mario Splivalo User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.15) Gecko/20101027 Lightning/1.0b1 Thunderbird/3.0.10 MIME-Version: 1.0 To: Mladen Gogala CC: "jd@commandprompt.com" , "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> <4CF678FC.9050003@vmsinfo.com> In-Reply-To: <4CF678FC.9050003@vmsinfo.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:5.2.15, 1.0.148, 0.0.0000 definitions=2010-12-01_08:2010-12-01, 2010-12-01, 1970-01-01 signatures=0 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0 ipscore=0 suspectscore=0 phishscore=0 bulkscore=0 adultscore=0 classifier=spam adjust=0 reason=mlx engine=6.0.2-1010190000 definitions=main-1012010082 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201012/11 X-Sequence-Number: 41463 On 12/01/2010 05:34 PM, Mladen Gogala wrote: > Mario Splivalo wrote: >> >> >> Yes, as Mladen Gogala had advised. No noticable change in performance - >> it's still slow :) >> > > 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: I understand, I did as you suggested. Begin; Set constraints all deferred; select my_insert_drones_function(); commit > I was able to insert the same value twice, it only failed at the end of > the transaction. >> 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? >> > You cannot tell which part takes a long time, select or insert, without > profiling. I certainly cannot do it over the internet. If I first select to a dummy temprary table, that SELECT is fast. Just INSERT INTO SELECT is slow. I'll try what Pierre suggested, on whole new filesystem. This one did get quite filled with thousands of files that I deleted while the database was working. Mario