Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1baVDC-0001Xi-Am for pgsql-performance@arkaria.postgresql.org; Thu, 18 Aug 2016 21:50:54 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1baVDB-0001ev-Om for pgsql-performance@arkaria.postgresql.org; Thu, 18 Aug 2016 21:50:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1baVDB-0001eN-1F for pgsql-performance@postgresql.org; Thu, 18 Aug 2016 21:50:53 +0000 Received: from mail-sn1nam02on0071.outbound.protection.outlook.com ([104.47.36.71] helo=NAM02-SN1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.0:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1baVD6-0005Ao-GX for pgsql-performance@postgresql.org; Thu, 18 Aug 2016 21:50:52 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=BlueTreble.onmicrosoft.com; s=selector1-bluetreble-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=qzLZ85kdsVMoIyhU7IEnwYryf1ox6nh0LIpKWlmSFaY=; b=aPXLyCSMh8R9fipN9pMxVQpqmayWuN6Nqvz9Clm4JK1E9oiiK+GyLMZun3FNzTVQil9Nwot4lxfwiAVrHRV1FfBJzfRp0AmIJZhFtBi98fF8Wqv0PaJ2nD9P/CYVMJNHvYYIrauJZaQP3P402TI3c/+hmhORw2XVBTkKzl6yrqE= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from [192.168.1.8] (97.77.122.57) by BN6PR11MB1633.namprd11.prod.outlook.com (10.172.23.22) with Microsoft SMTP Server (version=TLS1_0, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA_P384) id 15.1.587.9; Thu, 18 Aug 2016 21:50:43 +0000 Subject: Re: Big data INSERT optimization - ExclusiveLock on extension of the table To: pinker , References: <1471434303741-5916781.post@n5.nabble.com> From: Jim Nasby Message-ID: <0805fb3c-ed48-bece-690c-5e60c7b40e65@BlueTreble.com> Date: Thu, 18 Aug 2016 16:50:39 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0) Gecko/20100101 Thunderbird/45.2.0 MIME-Version: 1.0 In-Reply-To: <1471434303741-5916781.post@n5.nabble.com> Content-Type: text/plain; charset="windows-1252"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [97.77.122.57] X-ClientProxiedBy: SN1PR0501CA0006.namprd05.prod.outlook.com (10.163.126.144) To BN6PR11MB1633.namprd11.prod.outlook.com (10.172.23.22) X-MS-Office365-Filtering-Correlation-Id: 94a86e40-5c0f-44c8-e4aa-08d3c7b1b421 X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1633;2:L2GTFEGO2IK9reTHuQGzip97l7fDDph8cVIHD2ZWmhBy2e+K0svqpO7R+NHeZrLxRnMIJsVQeZ+qql5Dh7IoR8Thg/FctnYBsuiffYg1usxLEN+Gx16uI/38iUXYD4NbwUjwhGB4UzAnRsiNPxaxheaki6M17RrAlbf8WHxOHBvK29NYL4uZvlaSjRHzLP2u;3:Pt0b+EYjsnw9Xl2GcnX6EBqpxIRvP1QM8c6kLifQEMnpbIuk5l0cAWJwzSugphq6VCCusyzAcqvvjY7wLorn5voPbrYpfnWWzYI15MbgtXEY06Z57/JvyufpbmUZmyzZ X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:BN6PR11MB1633; X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1633;25:idjHby/ocDVUmb4tzT9YNj7LQ7RyEaHT6L2+eUAVzBGr/rsUYjB0YwvzmuLplaN1fVhHMvFC1SKCGHjL7gDOSKjPP3W3XFMyoSzqZzrVpO2ulMalRbVaiPJtDw+zOJv8XFJJ6rMqWm5u4b26Bsv3SIaRgLth5Mjsrp2uM/mEwSa0yXV4Z20s4L3IyWikTR2HsFRPPFnC8tcD6OhO/g4RfvCgkgxIoHHgpzkC7mv0iFX563/o3jvOdYntS4oTka3Ywnk9CkyjWX2NDyeJhDa5j1058XEvaZcJaHlOoUBIOrmjsG8tiQAn+pRM4tLcnTfWdmjf/4rE9vjF783oyucyqjvTh1jeM7nxV1PD7G+RC41PODyYOepLKrfnpfnPzLhEmdgJCRhPYOVlSwktPAHSRWCO3pLUF+vuvxl26uK0NsRfNe/cllBQ+zdweWrnUWlcvfrfTrN+L/Zz+ZlD3oNQs6D7VYURI7T7tUjiDGFJ1C2wheU880Y0wUojxKYII865LqdMIYSYjp7OMpRPdApFgr3pfs39/yUx0j1NVdH+Hn3ldeVj2LoX4ZokU2TtsIFcVYopaIVlBtyA2Wa/EtlQQfTTNEJALaetPjFLRKBu32RnerRDGejwsUxzxukZ8jNLFwzpfCtNzXlaxwoLk/kmYnkXDEXKfA/fFprEl48BPDjB9v2e+16c9lSb67XeEIQNL6y6o1zaOYRMF8BDc0Wps/JRFYJ2GIvrmIqP8t6WiyUeDKKno47ZWMVLlTnGF2wVI0S6qcw0jX9aNW1O1vvvxJx0OFidVMWRcOu3gBPpThM7yQ39r2eQoF1RAL9ByOBe X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1633;31:jOkktzeFBo2J+8olubu/B45AyaMSxlRHa1iv9Hom9uHM435tYB3THBNtxLUkMjfOb25KUPukkSldP06FrYKHD/sFKiyackl0/vcnq362R7mXh5+zUk1drRFfan4fLJFQEddUoBab+3isW6hPdT7DSb3sx2pgka2hxCktUHplXRbsgacW2DrNM4jVBFvbdVxgYnJrwqsl4rwHAyV3j7WKfEUAqZmKfMluwaXuP4IDkKc=;4:Mh6vQHg6epAkgByM5TMCbKXJujv5s/g/Kye11iLdOWCUpS6WISU70UEjz5NBJBKOoHlEPSyww7EmQ5sXtviz56/5ZJuWxSSfOel5JBbYBFmHplMXnYUvgORQ1RSP12GpoeDl5thQV1opvfTk2c67MCpQyRGhAAPU/SglDOP089atsWYtW/urt8WKcPJpmNs6xvaXmdjPq1lHpmWkLql7fHWE4giUTqLVoVVJGugHt5cMHgdyZwqcnPBpPQ1B3AAJckV4lOpAHNUWAd/yKzA3X42QyQWx+vJY25EKqFschDP21ErG558I6uKbkDXT5taMTk2h5I+xtm8+X5VqnK0/kAkTGRHIXvad1glHdhN6LV7KPp04sUS4OuwVYuibOwuL2EILDAiW0EXx2xby59A1eN6PBBLCVvssY2M12hqLKE8= X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040176)(601004)(2401047)(8121501046)(5005006)(3002001)(10201501046)(6042046)(6043046);SRVR:BN6PR11MB1633;BCL:0;PCL:0;RULEID:;SRVR:BN6PR11MB1633; X-Forefront-PRVS: 0038DE95A2 X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(6009001)(6049001)(7916002)(24454002)(189002)(199003)(377454003)(42186005)(305945005)(2950100001)(230700001)(86362001)(19580395003)(106356001)(77096005)(15975445007)(2906002)(83506001)(23746002)(7846002)(92566002)(105586002)(47776003)(5001770100001)(101416001)(107886002)(4001350100001)(97736004)(31696002)(50986999)(189998001)(76176999)(50466002)(65806001)(68736007)(33646002)(54356999)(36756003)(586003)(31686004)(8676002)(81156014)(117156001)(66066001)(3846002)(6116002)(16601075003)(64126003)(81166006)(65956001)(7736002)(65826006)(85282002)(2101003);DIR:OUT;SFP:1101;SCL:1;SRVR:BN6PR11MB1633;H:[192.168.1.8];FPR:;SPF:None;PTR:InfoNoRecords;MX:1;A:1;LANG:en; Received-SPF: None (protection.outlook.com: bluetreble.com does not designate permitted sender hosts) X-Microsoft-Exchange-Diagnostics: =?Windows-1252?Q?1;BN6PR11MB1633;23:NPTp56DcgnhACmF2qjo1iHnhpndqNFJyr4evd?= =?Windows-1252?Q?TbEiBodm18ueX/9yRyRPiWZGaj2acy3TaC2U3GaIJ1T6JNl6IhMbLWG4?= =?Windows-1252?Q?Foq9FtAPVZ2OsW3ob7SJQxnMKCRVMG5jmvJSyONHiWcgnLJI0XEc9RgK?= =?Windows-1252?Q?piesXu+mr6iIyHL9T5Zfo0tZl9xwf48/UeVs0g6GOKXbaM3TNwXvXCbc?= =?Windows-1252?Q?no4qyEwBuKsXA4vJqH60jhwKmqyzJUm281WdwSzDBJHWdXOi+l8YXUTR?= =?Windows-1252?Q?cs3etmRbAF29xksQ3YDcF+PsdrQgoGhq09m9UWp2q7o6+LzJ6fwmgEuv?= =?Windows-1252?Q?7bGhYd/+gc9rIyx6eIVveva78TM8VdA3GCP4kP9rdPsBfrkjpfChysVR?= =?Windows-1252?Q?AFg3hQSspPdlNec04kiBWulRIW2ZK9uEMrapmv+tzUAvZ4da2IyKYwJZ?= =?Windows-1252?Q?nyogmIOGerdfIcQwoINygiQK8wD5ocAoTaAS1f013YIZOPUo0R6B/Ycb?= =?Windows-1252?Q?3zQhNAb9BIyx1xmsmPID6ix6asuQtEpHYLp3TZbc7912kgxPeQWYj0a8?= =?Windows-1252?Q?qnPZM01GanXNTWxC2kBTZwZsWn4B4W8PhL5kOGzNQRwKVAaqXBCvzefr?= =?Windows-1252?Q?RFtsNUIuiAvSDqaVzX5dww6SYmvL7fG5fBg/JGvWL3HYDSaaZl7NySh3?= =?Windows-1252?Q?xDGCWkYzaQ8pJt5IEWVV5PkbZtGbYLl9BcxwUTVP2XbrgURyyZnT9Cre?= =?Windows-1252?Q?TLESg1vceL5uzFotR72xHrkft7zLdM+oqyNqOMFmt5kb0U+GYCjuqdXr?= =?Windows-1252?Q?nKx8tZjF3x7RH8PgM5qirR2NDqYJ/2ruKvXcYHYKQGBtQIwml16oH3Z1?= =?Windows-1252?Q?2ExgArV/qsnx3JAfmOirjtg5AM3D4P/IFeuBQ/ECDlSZl9qN48pEMp0D?= =?Windows-1252?Q?1yNfQQ1D531PYOLNHglVTzw5Caqa3UE9jH1bdpaLpXCruMOOUdkjkqEv?= =?Windows-1252?Q?M/SXjPETLNLWH0JEZfqVTzxWYoUD9SRyi6Rn8ccgrEje0Tgjaz1iiT4x?= =?Windows-1252?Q?6Uihnu8ojqyG7N0RAgq7qtuRSQJkt7tlMAbIh1ElUImaDgplzqwD4w+Y?= =?Windows-1252?Q?GBz9SGU7NmCfWFn4hhIhhWBbWiXFGWiFxng4nLPhtkXo2pJ7DAo127b/?= =?Windows-1252?Q?wfZcXlE9Qje4QPJf1I7amAUjrsmyfpE/SE+goU5kJwJM3gAILV9R6kdS?= =?Windows-1252?Q?VuPO06qnh8caLxdf9S9iqDP1Wzyp4czKLu+04JZkRzhYFCtCu1C390kP?= =?Windows-1252?Q?5YBLuazp4/Jnr1GGtvhxUXVD0mmoJV+2+HW6c6fWShrP/SIdpRLCDiZV?= =?Windows-1252?Q?Tr5VDhYU9epTeUUAGtTUibzquFZa6Cm4EmESSMLbppjdazjXL+vv4BYM?= =?Windows-1252?Q?MFlZVnnNUCjPbO2LmWkdQrnCC+TxUr7/th//RcPUw=3D=3D?= X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1633;6:RkNWEV+62LpEVp7hvdyCPmidB1WEqAlUYOgfqfL+OX/jbcta4QzdqjqS/4lQ3M+Aq9gSLO4bo3tnsYN6znKFiiGC1uWXebo9FwMTj/oVrTp7bNjggAZzom4ppoQnFO6ZCWxFQlk9sR1Xmd6hCyMH47eZ0eQiUD99QeFtpRPIieMAUuBXy7P5Gz8Xt9xvQfF9D8hIBYmVkzX7d34zHtIo1lZrqBxnzVJXsnVSePoInPRwwQdpFqnVzGUulyFgvkzyNjffmxhWqH4LBqjy+oWhKpvo+RjJ/Cb3dSUPaqlUsCh//GYHdqULgTDskMLFnLao;5:mlpoLEhg5VxMZj7gY+KvNFIsCTESm7lTvAqxjl9ALLkXPdnfXq5H0H07MYQeYxPy7tz2RDS4bcdPk/4Y09srBS1huQkDX8N9kO+3W1aYTZlyOMk83nMpBxMlqwQbydwZRbDcIo0R/sV/EJQ0TWzFCA==;24:eJTZPevbg2cY/9lfuMoioBaxUt2rzLV5o4PePYG7Ood3GVp/O6xxhy9hT0OrTYXU5Se/x2tAHagCJb2IgRGx0BetlpibuMUAp4HZ7uIEss8=;7:clwDz9AYNol9DVGcU0n2hA53XwYxjFcpifwxjlueKppuBdeqoE3FNybscE+o8/6YosJpT5OqphTMW3Y1w3dJFEXPDrZ9laieROo+S5hchrkdpg4Jam+wRTaBudGOyskDOWwv4go5GyKgja7xoN8LJf/YSGfMyheXVEQ1AOVLV0uy0Wss2BIzdUHPtEyqGlyASCY9PQjs84IZJbssN4NHN/5wz4b9axewSCzOrtFziDkzxX9jZJ+cERa9QM+iXTqK SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 18 Aug 2016 21:50:43.2248 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN6PR11MB1633 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On 8/17/16 6:45 AM, pinker wrote: > 1. rename table t01 to t02 OK... > 2. insert into t02 1M rows in chunks for about 100k Why not just insert into t01?? > 3. from t01 (previously loaded table) insert data through stored procedure But you renamed t01 so it no longer exists??? > to b01 - this happens parallel in over a dozen sessions b01? > 4. truncate t01 Huh?? -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance