Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bmpJD-0003bR-7l for pgsql-performance@arkaria.postgresql.org; Wed, 21 Sep 2016 21:44:03 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bmpJB-0002Fq-PA for pgsql-performance@arkaria.postgresql.org; Wed, 21 Sep 2016 21:44:01 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bmpHV-0000Kv-4p for pgsql-performance@postgresql.org; Wed, 21 Sep 2016 21:42:17 +0000 Received: from mail-bl2nam02on0086.outbound.protection.outlook.com ([104.47.38.86] helo=NAM02-BL2-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bmpHQ-0001sX-VF for pgsql-performance@postgresql.org; Wed, 21 Sep 2016 21:42:15 +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=koTWrLzSxl+aj0qFTIKo7FfVhPbCr8FKUOQLfL+V2cw=; b=X24PHsZBBJLDN0EEgjA5OmJ/AFfya7+qi5NihBubjtniZ4oYWi7nH4syr0gw+N9ZT+U9Uo2+6EOUg15SvaIvEpWHkclXOfOT/chgT0oonp8tQSI4swd6jahgL0rT3YWYzJzQSH8WUv2KR8sl4Umws/6RH3s170Le4nn9UxA4vhw= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from [192.168.1.47] (97.77.122.57) by CY4PR11MB1638.namprd11.prod.outlook.com (10.172.71.14) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.629.8; Wed, 21 Sep 2016 21:42:09 +0000 Subject: Re: Strange nested loop for an INSERT To: phb07 , References: <1f22a3fa-dcc5-eeac-a708-7f10fada4674@apra.asso.fr> <25256.1473691295@sss.pgh.pa.us> <224290ce-84bb-935f-1743-3f036ae3d508@apra.asso.fr> From: Jim Nasby Message-ID: <06b366f8-7e3d-ec7d-d146-1984818d3bcc@BlueTreble.com> Date: Wed, 21 Sep 2016 16:42:04 -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: <224290ce-84bb-935f-1743-3f036ae3d508@apra.asso.fr> Content-Type: text/plain; charset="windows-1252"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [97.77.122.57] X-ClientProxiedBy: DM5PR09CA0029.namprd09.prod.outlook.com (10.172.184.143) To CY4PR11MB1638.namprd11.prod.outlook.com (10.172.71.14) X-MS-Office365-Filtering-Correlation-Id: 871c8a74-5dff-48bd-0e69-08d3e268242c X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1638;2:16ncesJi5Zq0tObTRmxCPXcZzOm4TquBcugpcwPsEcx1DhXfk3M8AxQr4fVmbXTudHNJ3ZEgaeeVtVEs1JIXOOwNIY9FV1/GrePOJhJY7NgDsec+E6Tdz5eIU2xHWmMzImS8zWxolBzkxN23cJf69vS9uRpCzgUCrErbdzBL7UtmrQ2JFT91I0hKgTXBDCHL;3:DolWTBNc3bAK0mfTX7+DkrY5woKgTj5O/SEHDjNaH2wjSkWdMK7gUWXyqsCvoc3rAIZpJaR0ymCcXmhg+I/0OYb7RF1dqIo/1U953ASPKSCTRwf0s8I64FNgGEFE4ps+ X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:CY4PR11MB1638; X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1638;25:r2MO+ITgazxYVRwn5e1FYZcQIb27gclRrtDLz3InoLc7vJWCFqY7S67O5PRP/tr70Y8znOmjN9zFKbOas6V1ARC0oPFzibCDcZQw11N2wJl36l6RM1xqCnZ5tONDu4n+3JWQwseCDLlXwVwfzFgKvTJJr7ql9pz3ZDPsHIR/xzZ0VeMZWgN6Qvcyy5eytoZIbES5zY9VNzusD7XHLDJ2Q2MMMbM5EfmNFaL9eh/T3hNTDOGt5RzggKzl7ZJ8Z3Fzm//qLMryvSdQQAmGlAGLPW0Gxg5Sxv8ciN+dJ+tV4hl/0oE+ys77hgLaUCIX0g3B9yUwknxNfSn3td6SrD888/1m90xXCwSK1aL5TL4uZVwse8FtiplN8mVWzBufCytPLzwGnPvKdyhMihbe+s7dW6zx6V9CxHlduKIHteIEsE3K8X48P+Udumy/kL8TiACpMvVH53jzeVsXs9m4XHZNh0oiR1hnzSF5WAVXRjw1SPLDLPSOj1LAqTPiy2RhBKoEDJ7jtdCyOAi7bf19UdZKCWctUB5/Y/VAwk2VfeMxfVr6i7Y1LbbR0RDFT285HYLKfmQ28UIePz3NYkqprU52N7bc+vStPOd+uaCiE7RpYVqJI3dwjMftxRtCl2SGQwKtfBtqsdtXsZE/nq+d24itGWFOOME2XxHeHmBXyHNYWz+znzmeQGKsdqa4HUwsDwu3O6bNR7p7zBKIVyKa9Nae2Lft1vlNC8GQG1V6FrkxjFv2JcWDj1PwcHbCivLzbGBjH4yVJL8VtfAEem324Hn/3UGhGh5qM9q0hqds/rr0FHu0aJmd8OpeYzRKvVS1EYrr555/BIoNPIO+5ZttmhGPrw== X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1638;31:puaMnrfaDoYeos6py5W4zwXRWAFgpqbPqDAb8Txw4ZKHSgsx9g337Yx8BUoPyD/sx7UhqlgXoCJ/1v9mAZO6nI6TyCuOfhXJm1fQlxE8Sb109kHg8kWVAPQRQRIf7alrEJfgHEwO1uFnA9ZZPdswXZZ2AVbHEBkI3JDgCkNZeAymrho1ZginOhHXE9tFXcfP5HNeSYo2LiIb6bf6OYC66EPxiI1f3ZhpNYjv708i8Dk=;4:XU+sGcnl60kz3FiEQXW0rJItfWo1PGP0PrKPGKWUjcqhwU79f8YHiW7SLJGvu5o+zRVtyIRd2h8pqabK/wEnuEMbgtxv4Ug57hscwit9D+reThtQ2epGHAiYL1OvzZEkAu08UI0fc2kPxzp4BGekw+l6BWkMcqlRbovFv4ZKUzmi++fNBUvDBH+9vNZDMO9wyAAbhZspYUwN/Z2GsqcvnSP+pxap6m31mY3qABwU3t8SJFaZifMb2pJCMIZ6b+C2PthoQjEe8V6xdBA0SGwPyLfqAVywxdqQSJhnNFxhByVgxls1ccv7mV11b1SLHmkS9mEN5BezUt0Wyr/ytxe8dXls6VDQgvirhObWU/iZjynKzW8Oyc/86PzSV27T59MFgd0uxEFjiThWt6WS8IT//72cXkrJ2fSxN5SDjF+2GA4= 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)(10201501046)(3002001)(6043046)(6042046);SRVR:CY4PR11MB1638;BCL:0;PCL:0;RULEID:;SRVR:CY4PR11MB1638; X-Forefront-PRVS: 007271867D X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(6009001)(6049001)(7916002)(189002)(199003)(377454003)(24454002)(2906002)(36756003)(16601075003)(23746002)(65806001)(47776003)(42186005)(19580395003)(65956001)(6116002)(66066001)(3846002)(86362001)(31696002)(189998001)(81156014)(105586002)(8676002)(31686004)(106356001)(81166006)(83506001)(93886004)(50986999)(7736002)(68736007)(50466002)(586003)(305945005)(101416001)(117156001)(64126003)(33646002)(92566002)(54356999)(4001350100001)(5001770100001)(107886002)(77096005)(5660300001)(7846002)(2950100001)(76176999)(65826007)(15975445007)(230700001)(97736004)(2101003)(85282002);DIR:OUT;SFP:1101;SCL:1;SRVR:CY4PR11MB1638;H:[192.168.1.47];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;CY4PR11MB1638;23:pAzL7y3FdajXyQ8oK7JLB6GuRWdzUQwPYAXHE?= =?Windows-1252?Q?amOEoLmRGKOXOV41vy8shvU888OswaP5pn8b28N20T29OfJiNdNRIo7Q?= =?Windows-1252?Q?CrGrpurxP892Q2UFB/o5WG4DodfkYN0JCjb0Sk/hK4woXs6rXRtbD3+v?= =?Windows-1252?Q?sD0PeW04Hx4bKQ/e3NQRFFwcKvRisozE4yD2h7KpXeDKLQJznqFdd0dg?= =?Windows-1252?Q?/RmI2GgSAtZ3MnfTbQNhJJO2pn3Wev3FW7P82KKMiibpfz+qooR6twhC?= =?Windows-1252?Q?AA0lcWK4EICZiFVBUD6yaAbGEJ3OiGybJZHeVvQUZtO2eLDNjKadVRGq?= =?Windows-1252?Q?Lkw+hIBRM3MPSGnB7VyrHQ/Ze53jTq43FZ68b/iPFAUxfASnvJ9yGTw1?= =?Windows-1252?Q?R/c4ha3rXcUEPuIENs8rRr+gKQDK14WD7IdWta+fzKvZaqHpKG2wYIPu?= =?Windows-1252?Q?UA5aeP9n6yI9uyMILtm5hpECaVUZtZs0sC4LiZgwodE9v9FqaZa1nBwT?= =?Windows-1252?Q?LKjY83pS64DuTqP6Di5kIM14okgvyKCgYECBLXeT7AO7Jzrb8WGjMNK+?= =?Windows-1252?Q?/rlFAUjUlyo0T162uTkceKIaxFg7cdtb3shlnd0MQPYqcZ5setECupzp?= =?Windows-1252?Q?4N/OQe6e4qHeUesVblkrrbKj2gzRbioV3VYWtg9nt6c1Au48YFpjkon2?= =?Windows-1252?Q?R0uVq/GmsBeuWW7oWYuzqwBIyEcqU5KPaaAlBrv9aLkO9Q9oceQ6S4+o?= =?Windows-1252?Q?yDk1RZjfoWgXikWlSpfXhCadPMoN+8i1n1Hks6KRVMZTqdojmuoLLrlF?= =?Windows-1252?Q?bvDmKcsNWSClPg44YPVMnu4RPe4k/+zX7vBbYzcirQbNilBlQAjWJW2u?= =?Windows-1252?Q?Wqt4E6YJZRx+Bxf4svuw/RmuhlCQ1DOSkKFRG0s3i8SFn+3YjI0ZmgtQ?= =?Windows-1252?Q?x7wCYOhmB7Map/HcpHRXF7QDe0LquklGqXBNzVXo3nMU4GUO+d9XFhlT?= =?Windows-1252?Q?p80jiSx6lMZG30vldGdq0P+MSHjMpVDURNXf4xbYjdX8t3LlmEATlycU?= =?Windows-1252?Q?h7gq/ksJnW1CbKk4YSEVG4eGIUdcr7r6zGp7d2J8ZvfLEuSVo0zZjDeL?= =?Windows-1252?Q?wnlfEoz7Enu/5J/ty2pyJGuVsftxUL7yzGra+dJv9eqg0FgUNw4P+9gD?= =?Windows-1252?Q?1+oSOYaMUT/1PqLOW7FsZuBrsm2G4yS86PiczPYKUEsu0doAxTolUOBU?= =?Windows-1252?Q?1dCL59jWPZqiGKveaA8b0mmrNXDXFnE0uiDAAarTBioJAWOCLNVe5TlP?= =?Windows-1252?Q?c/SwtRkxWB/mIkou51i+/mgKalWuG/ohzuKJUosBPqc+T5v2C+oWPYfr?= =?Windows-1252?Q?aBM5pr2OZMSUYwW3BR5jgsGrc3REHh2lHAD0qvbp85UQehEQfS9eNCxv?= =?Windows-1252?Q?+pVUGTdyefHmmryYWKUqqhd5HZz/wHxzYiv8tfbgSKPy2M2CIRRCCZ1I?= =?Windows-1252?Q?7oqGLN1+cwVirACFquj0/D7n/QSTGPb/ugCc3g2JgCya1ozzg=3D=3D?= X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1638;6:Wkw/JfnCqWaPQkadsqeRcV8e0WQ+xphVlzqv0xHr8wlmOmtwSmzQAFhaDSW9AIcOcY7Q3EM1ZkME0ShYq8oer5SA80X0mzdUjYr5NrH/t+WX8P0d1+XSfn3kvezlXn+ZUlAhW4HjfPFbNRtmmLfDMkerHUCywCpGneeoGng6mCKK0bzpw9SxLR3jPOCWx6ZuqLwVEdabL+5P7VpRz1SRLQCXVq6817vVPaOF6Ge154yrbUvAd3QeXP+86JZVdSgSLxEhYPtiD+KKZv5SRaNcCTbE6qKYgJFdvHWGjxHIof4/4J2CwqUkTl/W4s2joPkT;5:D4dYvdD3HiuYlMcN/GIp0BHQNZ3vafrnLIGn2RXbRlxeX1hi29p+veYLTeOm4JCiCZ+Q+CyD8CB45GzmmjVmc/K1qaA1gLt7avJIk799WXWniiCoL1shkUMitwz9cd1lCBB6+mxCfeEd9vxjL1M0tg==;24:phsXbRbtbafIaxwenYH9L+YMZuJaOTEEL6gO7yMVYQbWVZPp1P7aBnCkDVz9uPGZ30/ayODMGT+NoFM7hnMsaf4jcrjPuDJfxth0fiLu0E8=;7:pD88TrmpYP2/u6E5qsZMjQxZ1Zjz3ZG12MlHaBWTO+IMvp9IXtJCml6PglNTphC3PKVcdSAQUIC5xOAlswLLQQvablwhJocBXm1iTuzSv6jeSlQJ2tfpo3fOA1sQ4+FKudmdpbe5YMZRUzsg9YhCM8QlVBRIwjiImnUL31leJ2xR1i8YbUMHmEQGtdBAARPLPyOZZijBZ69TfjNCR/N0FBecUEcrSL9k0CsoO3jI8obNdLoiX50UWTLk+POLYOrm6oIO2hL/V8PZsUd5GB0P8oEOwXD6QsZFOYozbMiXxVL6pI8oeg7Kr9Q4x2b+t43q SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 21 Sep 2016 21:42:09.8693 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY4PR11MB1638 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 9/12/16 1:05 PM, phb07 wrote: > The drawback is the overhead of this added ANALYZE statement. With a > heavy processing like in this test case, it is worth to be done. But for > common cases, it's a little bit expensive. You could always look at the number of rows affected by a command and make a decision on whether to ANALYZE based on that, possibly by looking at pg_stat_all_tables.n_mod_since_analyze. -- 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