Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bQmBz-0006od-AS for pgsql-performance@arkaria.postgresql.org; Sat, 23 Jul 2016 01:57:27 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bQmBy-0003Lr-St for pgsql-performance@arkaria.postgresql.org; Sat, 23 Jul 2016 01:57:26 +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 1bQmBx-0003L2-Ji for pgsql-performance@postgresql.org; Sat, 23 Jul 2016 01:57:25 +0000 Received: from mail-cys01nam02on0075.outbound.protection.outlook.com ([104.47.37.75] helo=NAM02-CY1-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 1bQmBq-0005xc-CO for pgsql-performance@postgresql.org; Sat, 23 Jul 2016 01:57:24 +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=pEtjOgelY5ld2PAURPOuP63YbZ8mFjyTQQoHSDXHD/o=; b=CUX95ZNwsPiu+YAiJ1LCpGnDuJfp9frez0abHh6/hOS6KLhWC+eGPNyE1cme6LpNptTgDBGkLXnK4QqNS7t7IDZoZSYhUmb9artO9NOglJvcYf7zWBl2mq6kASmNrlU5efTUR4Lsx7ibZ0WPsf9dLDAs3fk9+rtKqPs6KwFpe/M= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.local (70.113.16.71) by CY4PR11MB1640.namprd11.prod.outlook.com (10.172.71.16) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.544.10; Sat, 23 Jul 2016 01:57:14 +0000 Subject: Re: Seeing execution plan of foreign key constraint check? To: Robert Klemme References: <4b2f737a-ea37-35cd-012b-307ee1c9a584@BlueTreble.com> <11766.1468959038@sss.pgh.pa.us> <15306.1469138384@sss.pgh.pa.us> <1e196503-3b9b-b16b-22a2-9e080faa4007@BlueTreble.com> CC: Tom Lane , pgsql-performance From: Jim Nasby Message-ID: <1420044a-d3ac-b503-9fff-e5ed29e223e7@BlueTreble.com> Date: Fri, 22 Jul 2016 20:57:07 -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: Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [70.113.16.71] X-ClientProxiedBy: SN1PR12CA0027.namprd12.prod.outlook.com (10.162.96.165) To CY4PR11MB1640.namprd11.prod.outlook.com (10.172.71.16) X-MS-Office365-Filtering-Correlation-Id: 17fd00ac-eee0-4df2-e5d0-08d3b29cab3d X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1640;2:Jr1nXzCOSX94opq/bvp5SgmnTpMLquEf6HH7q2kDdPs4mRGl9Bo6WccnFW6rgxRoeH8wGKateDq6K+n002fxJxM6HTnDEv5Gkvn8MEWzjdm7rOwFN311vOWTW5w+/NMXrJHiPoZfmWZMq7XpvrFbvP/ozqN55mtM1Qc8Ya5NSxRGZ5yJVZQtVv1fv688Xdjo;3:VH9QLsgnRXwrI9oivCT7tlNanD/oNMH8fFc6kqP5FSJn4eO4odN3KdnO0voT+62mfOwOD6Vy+MtEaGHDqLMSRJG+byCReXBtAgZYwNLXyrB55xtek7YBtIRSb0i17oz0;25:gHt/KzDrXJLZpIODXlUFT6L/f9xObZLY9g83YYWowjeDchFz6IEyb0nWGCZpr6hW8MIlnxGFJWVSYoEpcly4fYetatq6JbyQlNHRffGqy8dBoLYKq33LMM9MWBItNwaL1HpOJZyQYzc0O8niiNnvRLlQ47fJnMUd+F1gJfVrozsrl3baoGlHIVoA4Ibz5wv+Dlgsb7+gI3gxkhkxQ6FuYgYHwaYha3ype+d32kva2UNLw1MWm7aCuoeCedty+aKuoBt0mvmxiNQsG97jdPOFkaF7Pn8gEV6OlzApyGr0+rsBLcCvDsekRxBi319TsayatApUocQgQqqoYdD6Vt0qi0HJK3nTuHDITILAsmUBkQvJVT1VaHS2/Ssyvk2BSspd/ols3msRz7/9vG+jn+rc29KhvLYEeQ+XVSFch1vzpGE=;31:hk+fHeOs2qwte/kEKgy9phH51I+IUW7Uk6QVsNo3VX7/4qXu8VvJGiG7tkZTECFUpD/YnX8cZdksAv2sUQ4SjmoPHKCpvD/tOk7Gplwseyu9pTavh3Iv38DZy0OhDFfxrI+Nh9nWzJmDlHDwNpuQ4AK9CmZgPATbkld4V96FzdAjLR5AQcjwhsow3A7bd0C//nhc9Olf8TlX3C366cdvyA== X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:CY4PR11MB1640; X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040130)(601004)(2401047)(8121501046)(5005006)(3002001)(10201501046)(6041072)(6043046);SRVR:CY4PR11MB1640;BCL:0;PCL:0;RULEID:;SRVR:CY4PR11MB1640; X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1640;4:YYvp1uhX+c3vBVShzNbop8LzXTHMgtPiRikhCNNX+X22yzRlzP2MNCX8o036VXL8vFk7sX9pquBEXJ1QEGOoUGVwJijldH74qRe5u8CtV2xT3jCqrn7OVnxCDxTPdi4sLw9pSDs1FcC20d2RLU03eDjome6u+rhme97MHmzGATPTBc0st5R4DbcTkEn3QmBBCfNzXdgx18TPQWebsTxxRJJ2gZfK9fPH90ZVCdwfbo/Wj6qIzaSXKCNK+JWSj4D5zKdiUUPOibqtxbjr1ShAC+tllddfrt7p/VxCbbXaS1f6LquY5oQlVrqdS44n2M8p/IJajt9NRuZUyPKbZfcnTvz3sUzCrWJuB7XYQ/q5AQIUackeuAQreE9YkJUAVjecue/95LS7/H6ZPU4bdZkQG4IgQmdIzlDG4nQNZI3FLsM= X-Forefront-PRVS: 0012E6D357 X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(6009001)(7916002)(24454002)(199003)(189002)(377454003)(66066001)(65956001)(8676002)(68736007)(65806001)(47776003)(31686004)(15975445007)(42186005)(92566002)(77096005)(2950100001)(86362001)(105586002)(106356001)(19580395003)(33646002)(50986999)(76176999)(54356999)(101416001)(83506001)(81156014)(7846002)(81166006)(31696002)(7736002)(305945005)(4326007)(2906002)(50466002)(93886004)(23676002)(189998001)(230700001)(110136002)(64126003)(6116002)(3846002)(586003)(97736004)(36756003)(4001350100001)(16601075003)(65826006)(85282002);DIR:OUT;SFP:1101;SCL:1;SRVR:CY4PR11MB1640;H:decina.local;FPR:;SPF:None;PTR:InfoNoRecords;A:1;MX:1;LANG:en; Received-SPF: None (protection.outlook.com: bluetreble.com does not designate permitted sender hosts) X-Microsoft-Exchange-Diagnostics: =?utf-8?B?MTtDWTRQUjExTUIxNjQwOzIzOkorQTlTRk9yc3ZzTzliakFMb01SRGp5cGs0?= =?utf-8?B?OTBhaHJlUVdoelZXZVYzTGpFOHo1bjVZUkRFc2FKcDFBMit4ZTZuRXFUT0lB?= =?utf-8?B?azZwTi9ZWkNzbjY4ekM1Y2pUeG84VUVuUFBsRXJuWXQ2YnNMVFZRTnJRck9W?= =?utf-8?B?dS8zVThYSlNXTFZ2TGVod1dLTkl3Uko2TGFWY1lDWXowazFBeVlVOGpkakZW?= =?utf-8?B?Q2NBZWJZQU55RWhtcWlYaUlvalB1a2RkNHJBU3M3TjRGYVYxUkJwRkNGVUxT?= =?utf-8?B?Zmt1WVJvNnBiUUp4cjN5YlpsY0FNSHhucElZbSt6OGZPaGtMWHBUaVU5UDRy?= =?utf-8?B?c0hkcUZHenRZT01KR2l1SFlpYnc0QzI5RENaa0RyZUI4WkNnbU12eVdXT1RL?= =?utf-8?B?bUV2S01UZi80MFRsNnRyVTA4Nis1emw0NWUyN3A2Smk1c1ZQR0Y1Y2xjMWhx?= =?utf-8?B?WHdxM0ZnRENkL29TSlhJbzZ6OG9KcVBIS2ZMRDVqcHFWa3pjTEJ4M0R5ZW55?= =?utf-8?B?Z05DRlZKeVNOV2JtMzl3aDkyS0Y5bUxXQTUzYXNhU1BRaFQ4ZGNnSGJvcUpG?= =?utf-8?B?V1laeFBSR3VvcmdmVnQ4eC9EYmI5c2pac0dHbFcyR3RmalhaaGhPRlVFcjVX?= =?utf-8?B?NW5GNld4SktJcXFGNHZFd1dFdFpJdTVPcUk4eGtEUHJ1QmpHK2hsQTd2SlJp?= =?utf-8?B?TEF4ZEJOZGlhaVZHRUdMZ3JtZzZUSGMrR25xMzVQOFJZUUg5YVVFamI2MCtD?= =?utf-8?B?UHNzeTRObW5NdVI3bjFqS3orcEJzVWJGbFduY1hrWWpSM0FMeVh2ZGRPS2xN?= =?utf-8?B?VUJEUmJrSktGUG5iUkRSRDhNQUtDcUtlVDZ0QnRiUWxpTjlNaGUyVlJjUTFa?= =?utf-8?B?T0U4TlNTRUVxblhZbXdJV2UrK1JMU3N2VWYxVnMwMGFNVjlqUlpJYW5OQmNx?= =?utf-8?B?SGorSDBLZURycjNLbFZBRWloUUFOS1BwM2kzcDlvZ2I5NGtQUE8yd3ZmNkc2?= =?utf-8?B?d0Z0Z3hoZzdpcC9qZWZXVnV6U0wzSXN6NjBlZHk5NS9jeDRHT1BWVjhIU0RI?= =?utf-8?B?Zm0wSzFYR1ovVUs4UGlQa3VJMVNlZUNSN0RmRUhJM0FyTEZ5aGFRa1FoZWla?= =?utf-8?B?bmRPUU5hNEp6ellEaXZOeHFtQlY4N2pGOG5yWEVVUXBvK0FpNmQ1b0p4UWJm?= =?utf-8?B?MjFDZG4zajAra2twWDEvdXRTb2txMDZxUzl4VE9jblVDc2daTnBuMk5sNnBs?= =?utf-8?B?dXg0cVdWU0pTckh3dnlYL1ovdXJoZWFKM2ZrdWN1b3JmWWh3ZEJqQWlIdno3?= =?utf-8?B?THVkOEFKRjBuTDU5cEh3MVN0U0VneVYySmh2d2dIWkpYLzFUdjlFVEdpNCt1?= =?utf-8?B?UUV6WG1MdkFRR01FY0tHN2FZeWRHTjVxS0xaMjE5TWZ2YWd4TTJiN3Eyb3p2?= =?utf-8?B?Q1hIL3FlbjF5VHo5THRUM1BzSFZJNWpwRFNBSXFkcjdHMEtOUnFJYmpVR0dO?= =?utf-8?B?OXRWR2NVbVpOWW9HRzZPVGVyLzhPbDZxWGpBZlNGL3VaZ1c2eVRVdWxFTFVj?= =?utf-8?B?UnpvQWtFcVN3djIvUXdDY25NU3FPQ1o0Z3hwN212Z1pheWw5U2JIQm1ZVlQ1?= =?utf-8?B?YWg4WTUxa2d3QndYN2RxZDYycS83aitVb3JPcmozbVVTaDZCV1FKV1E3NmNG?= =?utf-8?B?dk5vUUR3RStWT2Nic2VIY2l4eUxOSExpMWh1Y2RPZkY0b2pENVJxdkNUV3Vw?= =?utf-8?Q?0wetPzHEvm7SKO1PhOc+fXDCYp5V8TL9ZbWyY=3D?= X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1640;6:JtjwZqef61+aOKdFGp03RW52EStOfR9J/V9rsK7swm1eQrhIuzJckIWPQLmZCY6NzcjGjNOFUanUZtOtyj3Tsu2jLLx138kg+KO/h2kvZvem021NIFgOYmiqOnaU9xKSboHqVyBj9eSJgzGzP53m5KVarRkHGezkkOvFfE9Jhau6NtQXVTw7gBgmPfMmzT85iOcMtlUnazygInmvvA2V8sZR1IHowGiY1RjBaSB43rLhsr74OYmY2O0Win74eSu6AR8DeeTxEJ8o6MUm5pu3Mqmlvcb3+iHMhvBe/sIqhvB/i0zcj9MrZKq0EFMUKnmm;5:6nI9cqR76B3WJj7y1C0pxJvaXTvyvootGF0F3JEwqtmBpbrscnAquHSaVg4DXBLS4Ia5Jc7o33ugUijsYmhIsK7MwE9qHl/SPSAyWdETKHsZ5U2KpKUQEBP6OP3/BreiWUU0ehP2NiQGlYmv3OvjgA==;24:0h6mKueisSF2LbGTGSubhubq4S07+uK4pZ1Km+XVe8TeiH5KUujv7FxkZbeqW+AJK3xjB5mzECc/QRdGuN3uKfk7ZH8g3xC3QWKxT5MCC0M=;7:ISxTYr4Z0rnCzIo+9es+0CH/13NqESbTZODGt5NJrb2mRiwnC8OjxZPYhkqbDCLbCS9e6EQv8gynRZiWyLan7+cOQjmyYvUED634XQBzdJsJC7Mi0lrG24KT1Ms2kAHgoHvoh8x0o8JjLbuSW86XvC699Z1RJq0OJnKyJLPkuDFkGlC0mITn1XztwdoQgPKGaWf+uu6kAuWC+B+r8kfLI8dgix5aj3IPsp/CccMdF4ZYpHrE/ycOclRy+H30vX5d SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 23 Jul 2016 01:57:14.4382 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY4PR11MB1640 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 7/22/16 3:37 AM, Robert Klemme wrote: > I am wondering what to do if the same statement has multiple execution > plans if that is possible in such a scenario. Present all the plans or > just the one with the highest impact? Show them next to each other so > the user is immediately aware that all these plans originated from the > same piece of SQL? plpgsql runs all it's stuff via SPI, which can replan queries. So yes, I think it's necessary to deal with that. That said, if we only kept the most expensive X plans from a given function, that could handle both cases. -- 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