Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bmpNs-0003kf-2R for pgsql-performance@arkaria.postgresql.org; Wed, 21 Sep 2016 21:48:52 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bmpNr-00042Q-Ia for pgsql-performance@arkaria.postgresql.org; Wed, 21 Sep 2016 21:48:51 +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 1bmpNr-00042I-4J for pgsql-performance@postgresql.org; Wed, 21 Sep 2016 21:48:51 +0000 Received: from mail-bl2nam02on0059.outbound.protection.outlook.com ([104.47.38.59] helo=NAM02-BL2-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bmpNn-000415-Dy for pgsql-performance@postgresql.org; Wed, 21 Sep 2016 21:48:50 +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=62RQjdfJ987bl7r/i/QX76ZEXWiqDvmfItCCeYkJeII=; b=B9MiFnaYpgG9jM1ElZw7JRSeceLIpYlVjmz/DnFoJfHl7gHgsAxreuYUiO/6AmNnm7Q4NaT6WKt7VRbH2cuDDmW0nvupj8MvjMiNcoi+WrAOFQg7iRi5fhMdho9RLmoQrv9zutt8ORwuFmuEJUacOgAHXIKycz3qm2bl1a2Sx0U= 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_0, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA_P384) id 15.1.629.8; Wed, 21 Sep 2016 21:48:44 +0000 Subject: Re: Problem with performance using query with unnest after migrating from V9.1 to V9.2 and higher To: "Knels, Udo" , "'pgsql-performance@postgresql.org'" References: <1d6ce510f1cf4dfcad73b15ff8ff98a4@EXCH-1.ddd-do.de> From: Jim Nasby Message-ID: <283046e4-a37f-d8cb-85dc-52d1286e8ffb@BlueTreble.com> Date: Wed, 21 Sep 2016 16:48:41 -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: <1d6ce510f1cf4dfcad73b15ff8ff98a4@EXCH-1.ddd-do.de> Content-Type: text/plain; charset="windows-1252"; format=flowed Content-Transfer-Encoding: 8bit X-Originating-IP: [97.77.122.57] X-ClientProxiedBy: DM5PR09CA0038.namprd09.prod.outlook.com (10.172.184.152) To CY4PR11MB1638.namprd11.prod.outlook.com (10.172.71.14) X-MS-Office365-Filtering-Correlation-Id: ad55438c-1793-4865-9f77-08d3e2690f4c X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1638;2:u4B5vSvpBoaz+iwphzVpoKyIV4Q7y2QApPQLJDHGOLLoBawkcC+hcveEogqFH/uHlEhYjwfIOXCvgr8vBTW4Gco/HObknTEiWJWsCM2+NxLr1lBsYJx2zGRtfko73ouBhmJU02gp5GcIFGeBj+PCumdDIGk2E4fF8u9TpteEvp1s9rR7V1vuACwDkNtcIoha;3:EOvSM6KHB09I3DCyHYDtEnxB+Qe/Sx+B0bnIQmR/nAEw1qL5dT1sF7CC+y02w78rmvw/ZzYopaP5eoODVFzx/of6CY99+/vtQjXvoKUv/yn+SNfxfkg1Slb5hrPGp6RV;25:rxLQ0FPZPosOaQM/wucmESoqurRzkaVp2d3zlHXW2ZjqPOxNoninSPW4P4jZLZBo3/rUHRavRo5CIp1N5kLxhZW9x4VLo0Ct3OmjqZzhZxv2OHNqg12XCr88SysKVtabgdXUhyH32v/fpuQBiH+0PIoTKCp7ZVEVfqAyWcZpgL8Jt8mzg5ZFVDpejv1dDHdkKBTRZ8GF7h5fC/s60IqWn5cQ7+nKt+aTTZRM81p98NNMel5WMPLTCnKr53/bBDAl+Iu/KRyVg8pzYUl72vngssOfYZztxxnC0rGNzZ6pzlERODSMqhwCX2GaxSguNOJWzqG/frm48SSp6nfVszWIF+1ZmWp3jD+GH2U2ntQWCRkfGDC+DNM1EJ8l4GE+5Kdu/XlYJqDM94oflkc2WyK7FzgnKYAz17e91TtL8ue6LCc= X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:CY4PR11MB1638; X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1638;31:b4KYYub6LglEx+ZrsHXVCkxghO/qQFCB4RivBSu1AQXcbeoAhA/iTswqp+bjEkdJOgxHqlqxWQqAVjVOwOO0M1xWaJmUwDxoBGb4hsyoKpLF/kdXk7WtcFyBC3SAwkLHLb0FSPGccpCXpMbY++kvgyGAxk+IqWTg53D2sHmFAxPKTY/4VQzHKZmyzPhtKE45I1UuKTdIPt9vykFC0oQ5GubVaBRA4bIgL2j/3/BIWao=;4:Q687rBM2JmqqLpMx5tdk9CCG8Ed4H4My363xWD6deL+X85rBtic1b/Nt2nUClMY+8jdthJinmEuF7wUUTtmJHwoVPt6ulZHYGmm4UQcwIHsYHs55PiuqGaTbi9rBmI5TnXoaVz12qZV68TGJunYY/2GxjC+otVCOURMJnsavLG2HNyWj/qv2zIK8PrsG/lIuo+Xg2ITOjdK6V50gGgeZ+zO9YNlryaXJrqwNmsFVt9bgIiVHMs8PIFO/7gAhWRCFqprr+8Idd1QUm5ttXPdVTC4+ZnQC5Z+2dHZcGspWBEMbvT6eaMSwMed2edH7meKzwB3KPrEaju6eMIdPgJz6JRBx1VwN0WYvLA+t39B88VRJt0JtY79o+GJ0WOzSjc7l4tM/ZHZYGOVFIwtN27vTkmrvlF1tf+HZIuuZFruuCSM= 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:CY4PR11MB1638;BCL:0;PCL:0;RULEID:;SRVR:CY4PR11MB1638; X-Forefront-PRVS: 007271867D X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(6049001)(6009001)(7916002)(377454003)(199003)(24454002)(189002)(64126003)(33646002)(117156001)(101416001)(54356999)(92566002)(50986999)(50466002)(586003)(305945005)(68736007)(7736002)(76176999)(65826007)(7846002)(2950100001)(97736004)(15975445007)(4001350100001)(5660300001)(5001770100001)(107886002)(77096005)(65806001)(23746002)(47776003)(42186005)(16601075003)(19580395003)(2906002)(36756003)(106356001)(2870700001)(31686004)(83506001)(81166006)(86362001)(3846002)(6116002)(66066001)(65956001)(81156014)(105586002)(8676002)(189998001)(31696002)(85282002)(491001);DIR:OUT;SFP:1101;SCL:1;SRVR:CY4PR11MB1638;H:[192.168.1.47];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: =?Windows-1252?Q?1;CY4PR11MB1638;23:F+Rm9ZDIBqBknoDMXu0dsbBgHfpckMbN+IGZP?= =?Windows-1252?Q?MY4HgfaejrPxc2ASVHwsoqy2DEK3goeYw0y9BWEr2cCd8FVEqXP2WT2g?= =?Windows-1252?Q?ED8vv1zDksALx9ABsmg/iXywFGYdjuQ5nCMGTKU72s0K5i8LE/uEGi8S?= =?Windows-1252?Q?mAsLv8dOAyuZ1MeowcQeEQwQk3Ob12LtuGv/KuYq0vRJ++BBSgmd07fF?= =?Windows-1252?Q?pWSI+pa1eZ/rsnqVmC9H4NPTDAwToxu6xjSfZiCcaG1P3LZrkgGw4gCk?= =?Windows-1252?Q?03DTUeQJHv12NqeXhffRo3HU7DcF1AAK1/0R6MS+miVpE5Em4NLI14xg?= =?Windows-1252?Q?P3bRjQhQoEd92oj9zHeJBT5ALYPvezZ6OX9qSMQ75bj050xXkMdLcwx7?= =?Windows-1252?Q?9/Yl/W7FfbXMVyetwMpNgE/99t7+Br1Q96Qi3k68X4ClS5xkvmG7pBQj?= =?Windows-1252?Q?HS1Lv+CXu853iIcHJLZsdebrj/oBuCMQ6Yg22aPKNrGtJZbgBzxQgUzc?= =?Windows-1252?Q?Y/rjeEDZ6UcpqmRZUp2j5ds/aQDzdorc8GjQrbeIA/7OZoBG5ibs6gs/?= =?Windows-1252?Q?57vCyRdxg/ZIgMTPqqWuobjzc0rUDBGmHy2OOxyE27x65mcGCSlh/AJ0?= =?Windows-1252?Q?37itLqASfgVeAQlyQZI+ahIO2ZPS3KxcUIEN3Z6KbXkOHWVZfkIfOfzx?= =?Windows-1252?Q?82b3ztJXfaxqQdg7siKSsghnh5OVxKYYrkRtagQO6MBxlVjLdb+9qyac?= =?Windows-1252?Q?KTD3kuY/4ag3ZigcUXQwHICPlKurJHEU98AnQ2kiKJRS4RCQYak3gcVS?= =?Windows-1252?Q?Z0Bgy52dNHGlIjL6eiwffy/QLF8MnRamrvb4XBWPImpT/UtF3VKeH6tp?= =?Windows-1252?Q?VEo9uCfaMOjODay3+7J/bYi1anpV4M7rM+cxJPNirDMjANlUzJ4EEsFW?= =?Windows-1252?Q?cRe3yJrsKNiScy1mKDBJd98L7bkGWjq5+Zba5TBrWyt7T44zTKzQlKeq?= =?Windows-1252?Q?assNkWZx9k9hvl9txpXjrlNjLr+b+KhSNmygpc4QyKUhoyQfx56r4JXL?= =?Windows-1252?Q?12aypgBFBnGkBcCtr3KOFNDspwWvSIrmnL6cFryeNqOdccWEwaQ5QQ5M?= =?Windows-1252?Q?hjqFVpGKDObvsotmbDapfmOC4Gn8pfoqDuvPn/CmPxQwy6suJpLzAMMa?= =?Windows-1252?Q?j9giCEDVteQ6ous5YfQ3F43UkEoII0n9nWKTiLNM1kH/P91vEfVYLEqg?= =?Windows-1252?Q?iYdBzSvphVNDyAL2E9QEdhbxanE/co3rpbFgc0ixCorRNRnJ5TMVcZy1?= =?Windows-1252?Q?NNapBsgFidEPgS+RsArAzN0RzY+ywHLWFbgKI8IrohCJLdHAphEaw+IE?= =?Windows-1252?Q?KwLLusvkYut0W0TlLM0Vxt4rfHxmjCuMXfoUjsxKyJGyka0D2nBncZKb?= =?Windows-1252?Q?M+pyTwahLs/F82NPzrHYFNUWZ1PmAPgOiwOhvI/XEFLlEQsaOJBL3/Z3?= =?Windows-1252?Q?SSx+HMCZAUMlD394Zfv5nXqeU8+?= X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1638;6:XeQyrAm9yW7g3zvCN9sDy9QHbqmZDcoSOJhHB4bHuU8qznJj+s2gIl7nYG1YxBfkB9k0ecm7mkU5hn9n3SdNz6CSTxvyuIHGMfUuQRq6uTqDU+NJNv/RU28Vh25hjXkN42sU9S95T+UBgv16rhg14jTMoQx+H8ajyK3kcP3JbxKR0sOLvHBFqlYTz18w6wX0+Gm0jxwaMqTd/dG0iZlhjMiVZJANDrct4NY8fg+0GJl64JFUsUvoWPXOGisgraKDor6hbBhmo6laa3xBXpx9gaPe248w68Y7FmjJ7phOlziAIo2bpoOblHnHWDd1kgCC;5:uaeVeU01wlb3q+cg5O2HJqqrkgifSNEW8iStcnHe/JIdJiun/k3wFufN8WxTiw6e+kek6vXVJPvzfzyRHGFGwdlENmdtuD4cXC8t7RD18y4a1+C+gAUNzwJ818o4zIFZ0JtO22A/TzLh6hcOjpeodw==;24:F0gRq8xq4DBSOi8Hy/LTN9yyLkg9GzZuLtkPnffGJktxrvfjEe5pft8/I7xTyWoqWq0fp4uIDdD/24h5I+rQW2OpEWZvqon6MAqhmiLGsCQ=;7:JucQgU/jroIKzlpi0BE+JT2tvUDHR8XpsVNSdptdtolLsO9PobeyBc/D37B5iX54sf0sV6sKSowmsVeFMZslg5QHE+l4hFHiYwXrIpcO/ymGonbLKlky2IuMljMTRpEzLVO+aZICCeGD6Mu3PUXp4I0B6Nzn/oaw0eJosUWvbSyBHDEToPHcyiQdwIdV5iOY5VONwZuBr/uk4RhpuEPRJ3+/uD0T+lfCwEHhslot9Iafha8e7lyQirUcKoWLF6Ve5ack94Sa10aBB5qL6hOX77hkE+2ROC2hTr3fJRNdAYmHpcLW+6wMNoWvB+DPz1J7 SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 21 Sep 2016 21:48:44.3503 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY4PR11MB1638 X-Pg-Spam-Score: -1.8 (-) 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/19/16 2:29 AM, Knels, Udo wrote: > The difference is that V9.1 uses Nested Loop and the index table_a_pk. > V9.2 and higher don’t use the index. First thing I'd try is running a manual ANALYZE; on the upgraded database; the 9.2 plan you showed seems to be using default values, so it thinks it's going to get 100 rows when it's only getting a few. -- 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