Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1c257X-0001xc-Ck for pgsql-performance@arkaria.postgresql.org; Wed, 02 Nov 2016 23:39: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 1c257W-0007tx-Gx for pgsql-performance@arkaria.postgresql.org; Wed, 02 Nov 2016 23:39:02 +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 1c255q-00063o-GT for pgsql-performance@postgresql.org; Wed, 02 Nov 2016 23:37:18 +0000 Received: from mail-bl2nam02on0085.outbound.protection.outlook.com ([104.47.38.85] 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 1c255i-00009Y-Tm for pgsql-performance@postgresql.org; Wed, 02 Nov 2016 23:37:17 +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=DtIBFzpf451VAS/+8wCdSB/aqSl7BGtzGOgQb8cJJkU=; b=fkphknJxXQaVKGe8alSreQMosSBgqD1w/3eHGL9xVsz1YX4bgQUfsubIo1CZOd85tptKi33WtKVi66vZigeSJ8R9qrDP9ScuKnyTgq2AeNfmfx2CAaFOp/thGJPwXpZYshaBvjtQ9LH/2HfU2adrfAN7tHoermeqd4t8o37TJtA= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.local (70.113.18.142) by DM5PR11MB1643.namprd11.prod.outlook.com (10.172.38.14) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.679.12; Wed, 2 Nov 2016 23:37:07 +0000 Subject: Re: limit 1 on view never finishes To: Craig James , "pgsql-performance@postgresql.org" References: From: Jim Nasby Message-ID: Date: Wed, 2 Nov 2016 18:37:03 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0) Gecko/20100101 Thunderbird/45.4.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.18.142] X-ClientProxiedBy: DM5PR18CA0042.namprd18.prod.outlook.com (10.173.208.28) To DM5PR11MB1643.namprd11.prod.outlook.com (10.172.38.14) X-MS-Office365-Filtering-Correlation-Id: fb1fb692-df8a-4f64-4c84-08d4037928c0 X-Microsoft-Exchange-Diagnostics: 1;DM5PR11MB1643;2:Cw8nwFdjon7UEByccGdgj/qZgxAkcsq4wPmartt+1wqsUKWopKt8EInLJnMWm/M1b5LZNeogZK6fi1ixmC8HsTr+2tp95CHnNOEpybTjp89tA51vBI8FTs8yVsRAdY5kA1sz3InOB9yJbAcdp+mx/9KSOySxbx21+3Qddzjo34IbjovxI5CTUnYhgYBD6bIyKy+G1OhAG8a5Yrr2hhSu2w==;3:M4sGE2r7/lJO8Oq1/2M94aZrgIVCnD5CSKKKlqyCwkzf/EAvDAbczjX6jWMVth/lR4Jy0O5JautXONV3cr+tVu/ee8rSB2sP29M4NVT+fwhfoo0W81MtmpE4dFAp4NTLu37xV0IX4fMFT4TkaEGdJQ== X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:DM5PR11MB1643; X-Microsoft-Exchange-Diagnostics: 1;DM5PR11MB1643;25:hmXfzj4iYDqM5gAoowMdZK9U8fuEH/0OQlG44DAX0Z/FJtsaxxgO7uxqnatwtUhU5G6+whXROilHsID5JEuLTGLMoGFgN1Pke/jO8U/wa3+ef+6aUhQk80jx/NhZ+X4p38kGRxCSiBk1kneWcjDbRjDFJ7dX1FREt9pQDOeOd3NbWCtSQDvRUFursvZNlMlyo6imCaDQtJNGwnwveXgDXnACO2ZHTGLQCheOIcOLqDxl0xD2QDI9ro9tH9TakCg0oTzufuyXX/OyehKmoGabE3Vso8qkVSpdMGrdvVxqIafyNRxjFNdLslLqHJuZ+mSWFlBs+FpPWP3XFl1Ab5KaROSi5VW5w7Frbn9z+K8POUvM0ouJtxMlY1CinRXkSSIYosiHgLdXxZExF04wiPqunUkDZLWqASb7WDEKeG/QJRhcY+TfZ2VpGDpUdV2+63cFbtfaCawUcoSSf57URFbhHdEs54n4WMbx+6JMKPvrxWzgsli7HmI7jSfKUZIienAhAuBKR3h5Yl91XlN8lJQIW3tBqV7GBIevTJoLeGVpanwAsruAj2maDgaf8TASKiAlifi5K4OjXlT3oNhxsGQQHPHDJyqe8nza0D1qaZ5Jw2jl5obzdElftinRI+oekwACPd114Urhqee+MHbMzEKGGJiVyBY3VTKKLvJWolQsGJCxd+LCR8RAdIJP9raqNXbjucDM6jOxJUmiYjZhZKgPtj8UoLxYyID22EULtNlFLQqZQMSRQtPOhxACpWyRs/IbP8SopJiwg8BSduWamjkGS+1Fua63N1wAsy417BgL3LA0nUd1DkKsnym7q323Stum X-Microsoft-Exchange-Diagnostics: 1;DM5PR11MB1643;31:I/AuUtU8hzp9d+WsWtTsOlivnLifenyusuNwMZegQ2vnj30bFmqz5NltdNd0jAkz5LsYKlYbQCrMTwNBpuwxjtaNvkNmg1bDrgjkg9E9vXZmV2IOsxSC6eHgzvdDoKfJioJUAPqBo866p0i/o6NuA4Mj9X7ydXkO/ADgx1HtQsNw3iOuDCcfbe4SN5pKBWvcId/W2P5KIFRSQpqXlsP/tJ5G2DHto6YZMdJf50ma4nO1BZlZnf08FCk3tX+Ry8IQ/cXlArGsa1I9+C8w1Fw4Qv2UCJ4Pa/qr3qfN/MiW/YA=;4:C6TBtHx1LO1gbb5bYxeutiCTCdItZy4Nuu+D4IUYFYqx3EIN4ZFY3dNAnvEBGkNjEWojnNSfNislkGjbTfbTIL0Kzbch+8UVohCDuQxLgDJr8g9hI6azGvpObzekrpvCtXi77W633a2St/kYkMiH8HnQzAg077+LtBo40ke/UG9uEL8M50Kv87niUGPwjW/2FnPseOs1X4ZI+4dhcYN94MrhPuXpOby5N5VNThdywzJkol9rLlbPDBPStn6XqIXdhyLa9zjNEnE5sjLM8jlongLYmzkabKs7BQm3SxAGNr6VA4sSyGM2LJmjfcWiqUO4Ud+fWi3pxEaPh2fpIqul0cCHAHcXHwz08c3XVIQ5ba/y+t4j1xCQITpr8X+6s9to5jMS6fFq6KGP6f/LmKltZkXOgnQmkv91eA2sMAfsDytkVWz5elsYiMhygo96tYqZ X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040176)(601004)(2401047)(5005006)(8121501046)(3002001)(10201501046)(6042046)(6043046);SRVR:DM5PR11MB1643;BCL:0;PCL:0;RULEID:;SRVR:DM5PR11MB1643; X-Forefront-PRVS: 0114FF88F6 X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(6009001)(7916002)(24454002)(377454003)(199003)(189002)(2906002)(65956001)(5001770100001)(8676002)(64126003)(7846002)(33646002)(101416001)(68736007)(86362001)(50466002)(97736004)(81166006)(81156014)(23676002)(36756003)(7736002)(16601075003)(105586002)(31696002)(586003)(76176999)(54356999)(50986999)(230700001)(15975445007)(106356001)(3846002)(31686004)(83506001)(2950100002)(2501003)(47776003)(4001350100001)(66066001)(189998001)(5660300001)(77096005)(6666003)(42186005)(65826007)(107886002)(92566002)(6116002)(19580395003)(65806001)(305945005)(85282002);DIR:OUT;SFP:1101;SCL:1;SRVR:DM5PR11MB1643;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?MTtETTVQUjExTUIxNjQzOzIzOlF6M3BFWkk0MTdyUjZQeSsvRkpST2V6S25U?= =?utf-8?B?djdqSHQ5bk5DQlRWTWptaGZKT2pKYmNyZytMK0M3UWo1OWxUdTd5OTBlMk43?= =?utf-8?B?NnV6ZHRLU3ExWjVUWVVkaWp6ZVc1SjFTUEhvRGZ6eFMybHRNbTJxcG41NVBo?= =?utf-8?B?ZG9mVW9wWnpnS2VMelFiWU44NWtrL0M4N3FnbXF0VEYyN05BT2g2K0dHYnc3?= =?utf-8?B?WE9zcHhaT3N4WHJtdFhvYmJLZE9iSy8wck1BRkxoU2ZLYjZ0ZVdSSFdGeERo?= =?utf-8?B?bE52MURMYmcvcDlLOHBETzAraTByemIybzFSVTFQMjRxNFR1eVF4TlFkV1FE?= =?utf-8?B?YzgxMC9VTTdCYVYyTnlyYXpTbmpKcE1ML280QUZJODRERGJnaHU4OGd6QTZD?= =?utf-8?B?WHJmb25nNTN3V0NqNFMvZ0xQQmJQd1hEVnVvNmI1OFJXWjlKMGQzdk4vOUdH?= =?utf-8?B?KzB6SHNDdUVRTkQ2YW9DZ25zNHhvZzBBZFFzOG8zYlJmQWFuVy9lanZVcjA1?= =?utf-8?B?SWxZZ0FESzB3ZDJZMzlycEJKUCs4UjlHREF0S3NWZFVWZi92OEtsZnZQb1Yv?= =?utf-8?B?d0pnNDhqZmlHeDkvdTVRM2pvMjVpMkJmSEFnWEluejRnRUdqMmlhWEhSZG5u?= =?utf-8?B?dXJ0VkVCWU8yS1JDd25FYlVWcnFkbzl0ZmRlUElqYjdCK2hZVHFYV0FVUTMv?= =?utf-8?B?dWliRVJYNnFRdVFwbnIzdkF4S29kUjFEYnRaREcraENQQjUybCsrTklYekh2?= =?utf-8?B?WXFWSFpTL0k3QmxCcUxsYkdoMlhwb21NZlRjZkpsSkE4L3JLS1lKZGxQcVNo?= =?utf-8?B?d0MzQ3RzU1p6aFEveWdlcTZkYUN1VUFCMWg1eERiRmw0eHdGT3JreTZ1OFdl?= =?utf-8?B?c2gwNW5KaEQ0dFZtQmwwWVdYRXQ1eHhMd09SRHk1K0pkTmx0QUk0NXhqVnJm?= =?utf-8?B?U3NZY0UrMUJrYWVuQmJpWnBlRnpucytoS0hwRzlPNkF1NUxEM25yMUZwcXM0?= =?utf-8?B?NmNLaFVSZzFVcElod3p4TmpDU0FId2VhaHNmVFBuYXAxZ0RjQ1RLYzdhWnpS?= =?utf-8?B?SlRTMDVZQ1d4NGFVeStSVTdjMm5QNXJwVUg2UXowaFhyanlQRWc4VWtYamlB?= =?utf-8?B?VDRSTjZ3NU10a0d2YmxIY0JWdGtHVzk5eHVOVzZTVDRTTzhPdXZkQWEyNGZ6?= =?utf-8?B?UjkxOXBQdWZoS0NFNlFvN1l2dVc3UFptWmlpRzcwMjNJVVRIRFRvYzUyQnMr?= =?utf-8?B?WEg3cUtneGo5eEtWMEFFZFI4QnNaU3d0VHBrallSaXFKZTBJSitEUVJ6T0pJ?= =?utf-8?B?emM5R3g0Z1lLZDVNNlpuOXdXOGJ5VjRTc3Bnc29QendkbVhnbmZ6OHM3QXdw?= =?utf-8?B?UVNnOEgvK05vaDNCaUZjeHFDUm5WdHZqbUtHLzdHeSs1dHJVQ204Wm9QQXFW?= =?utf-8?B?dE9ZU0NBQnppNGpBRFpIOGVEeGZ3WkwzZkNKYVdMb1YvVUs0aE41NTlONVBV?= =?utf-8?B?Qjc3dFRUc3JVVFBJQURmT1JtTG10TUFva3Jmeml2U3lXbHJYeGZidXdGM2Fs?= =?utf-8?B?L3Z0Q1JHZjZ3dEMxRmxIVTI3T2FmZWZ2clQ1UlJVVzdpODI1bWlTNHk4aFps?= =?utf-8?B?c1YzN2I3UUIxQlphNGFMRCtVWXp2OElrNE0rb0lUVk91Ylo0Qm5kVU1oMTlz?= =?utf-8?B?cC91eUdwRFhOR2x5Z3Y0WE9UNmc2cWQreHU2MFFXZ0Z4VGhNZ0VTSkN3S0hC?= =?utf-8?B?emVzc3NCRDlmSXVkQUtYV24zOXpFYVVKUlRMMGt5eGUwRzBoYnhjRkJZUUhZ?= =?utf-8?B?RnJ4ZlJOL3NBalNHTVF3NDNaQ05lWFo0Y0o0MUs2Y1Ayc3d4bVBwRk5pSklG?= =?utf-8?Q?e6l1PfWPXQs=3D?= X-Microsoft-Exchange-Diagnostics: 1;DM5PR11MB1643;6:/eUMR9LnO9rgSmLggpJM3DS8Nar8a83HIrT1rwwykuRCzCawmBQEDPYSjTlvq3vPjpOYBRfmVGfyg8IlN8GUsEOIdiRV+YoT3Qy2BwQdNHjPQnPGzIykUb8codcCtdnqkYGdUpf5p0mxBIB+nCpwjQYauoKcwu5SUcQ4LaWYdcRWDGPjGt2NJb0Em7eYrEGc+6pN50nQ+rqqQYAV6N0ePlI/ulOLWb/qaE3vnxHpyPsm9jo7truVipIQ5dMY/bReuMqhaWsLegGVPMOf51BxUn1iM6M+4e2Qijs+BX9RlUQ6pGNMHUBK2779EFDcBkpF7VdolFuvjkpFCl4ErDUSIw==;5:EfcrwitJDjVyskstAtCHr4qPB0u1S2RGKgloHEV8zzWCJJSYoKsPXOgAnmTgbyK2moJsdmRnRtHrXEtl1gy6BTzBS3bxnCLO6eIESPzg14o6hot5o5/2fiYDTViOxfqUJ4fziWOb/prq1Ly31jx6dQ==;24:AkBZ5B9Mf1G4JncNhNwQ6/Lf397uZ4Gqw8+TnuMkQBw6NH5h9ld+ljz+KykjmlnWDMtdN9Gryjh48xV6fk+HkBoMVRtTVFSfA5FE8WMgkZA= SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-Microsoft-Exchange-Diagnostics: 1;DM5PR11MB1643;7:q8yuQwwXp58XFImXm+uV57mBqBLRCzIh2WVKW0Lg28G7TsT88y5xbyM5l4OqeNRkwWXW+Mq+ETkzTzM4zoEKjsfqf/AJwC+0drKHgI6n32iZ3Ql5U1X5+AFMpm5OhzZ/2VH4j3TommlqXHzzVCYhQN1MUdImo9LPEYql4pAmIAQa54bWoiYb/qeZNshSXoXF7t4Gm6k66FkBiPJG2FnxGHvopTu6o0Dg3s3HkNqOcSKRrVNOThc9KGLAd60Lk8odCQd9kiwVsLJSqyN8xFZFxHQ+PUAS48d8WIM66gFzFCS0NDRnsSUGtCOLruiFAkZ8siA6M7DTQxujg93r/Pw2yjzoWYfsJGLNu1qb7lsubdk= X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 02 Nov 2016 23:37:07.1166 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: DM5PR11MB1643 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 10/27/16 3:46 PM, Craig James wrote: > > Limit (cost=3264.63..7193.14 rows=1 width=4) > -> Nested Loop (cost=3264.63..428658697.57 rows=109114 width=4) > Join Filter: (rv.version_id = sample.version_id) > -> Index Only Scan Backward using version_pkey on version rv > (cost=0.42..6812.85 rows=261895 width=4) > -> Materialize (cost=3264.21..5992.06 rows=109114 width=4) > -> HashAggregate (cost=3264.21..4355.35 rows=109114 > width=4) > -> Seq Scan on sample (cost=0.00..2991.37 > rows=109137 width=4) > > Why would this trivial query run forever at 100% CPU? My bet is that there's a lot of rows in version that have a higher version than what's in sample. That means a lot of repeated scans through the tuplestore underneath the Materialize node. If you can remove duplicates from sample and get rid of the DISTINCT, this will probably get a better plan. If you can't do that then you could try changing the JOIN to an IN: SELECT ... FROM sample WHERE version_id IN (SELECT version_id FROM sample) -- 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