Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aEg6X-0000Oy-OH for pgsql-performance@arkaria.postgresql.org; Thu, 31 Dec 2015 16:29:33 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aEg6W-0006VM-7m for pgsql-performance@arkaria.postgresql.org; Thu, 31 Dec 2015 16:29:32 +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) (envelope-from ) id 1aEg6U-0006V5-Bv for pgsql-performance@postgresql.org; Thu, 31 Dec 2015 16:29:30 +0000 Received: from mail-bl2on0082.outbound.protection.outlook.com ([65.55.169.82] helo=na01-bl2-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aEg6L-0003PC-13 for pgsql-performance@postgresql.org; Thu, 31 Dec 2015 16:29:29 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=BlueTreble.onmicrosoft.com; s=selector1-bluetreble-com; h=From:To:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=kdpulLSJoQtwLURzbt6rPoPmdeccShheBdKwIxy+GKg=; b=byI7yKpGzzemx9/hJvugPdhg+xvNfevHkOOO0SmB05b/kfmOUFYaq4oQ94cQI20n8tHDj3U18JcYAxk0I9T5Uh+ZBoCbvxxtWQL/3KrfvCKocWQzO97XE2lVidqdXKZ1DxJeOo6XYvpcKpLVfCqFoVeYZfxhlwdC7xFShbaCBCM= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.attlocal.net (2602:306:ce8b:3460:65b5:cd61:fbd8:21c8) by SN1PR11MB0686.namprd11.prod.outlook.com (10.163.207.144) with Microsoft SMTP Server (TLS) id 15.1.361.13; Thu, 31 Dec 2015 16:29:15 +0000 Subject: Re: Plan differences To: Tom Lane , Anton Melser References: <10594.1451574122@sss.pgh.pa.us> CC: From: Jim Nasby Message-ID: <568557D6.8040905@BlueTreble.com> Date: Thu, 31 Dec 2015 10:29:10 -0600 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0) Gecko/20100101 Thunderbird/38.5.0 MIME-Version: 1.0 In-Reply-To: <10594.1451574122@sss.pgh.pa.us> Content-Type: text/plain; charset="windows-1252"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [2602:306:ce8b:3460:65b5:cd61:fbd8:21c8] X-ClientProxiedBy: CY1PR18CA0016.namprd18.prod.outlook.com (25.163.31.26) To SN1PR11MB0686.namprd11.prod.outlook.com (25.163.207.144) X-Microsoft-Exchange-Diagnostics: 1; SN1PR11MB0686; 2:ZAGTv2poBonI1V1mcO2wbjDAUqIFxsb7urQAxUlXr5WgN8FtEGCEKHEjJysxFDVzBTq/0d8seIe821zJjunzRr7XgzhA+6cS+jVC4t4liCDJZM30Ax3nZD7T1DvKoPpQ8WRTeduOqvb5qbi3N6DomQ==; 3:6ssOnKwGefVV2aIhizWRr+q3EDRUusGbt10OYnPZYI8nrE1knDJv7GYDgJ3zMomTcq8NAf19I8y43kyY4xNZWkK6VZjnVLFr6eV1sZL/uTpCjxAt5ly7bR0CKDQYI8TO; 25:GkQaBj2XP4UJXQ7TXEryYBYyuYJ2uedv/fA876940yHGU4Es3RYgi71dMXHGie483BMIPuXth+FB9CF36Fu7Z2DjFao/fqSLQDtkjL7uhhG1aQv/DVDjO2qPxbqBYbUIaSZdnLJDRt7jSlxADTqb5x8fQSucMl09DqvOT5uxoXqh3iwXHsWrXQSWSnEEZB8Fm2awyFkyA7TRapjZOW7ndgf9RZMxO7Aim332dMAg59XrgTVDW6bHUPY5Gp6HOwOj; 20:gybOwLCX14xpC2YcyoWj+kKLz9NAkbVKwieDQW/8unZpoE1UDG0Ktp0Yrqz+hid1It/aurr/VcdIpGqRpcD0hiW5PC7VV8WgdR/oEdCUvcezNEY+Pw974icqb6yJnfR2sOE0GC5m8nEvcy5CBxr5fmc+nOw764Szc120k6ua4W8= X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:SN1PR11MB0686; X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0; PCL:0; RULEID:(601004)(2401047)(8121501046)(520078)(5005006)(3002001)(10201501046); SRVR:SN1PR11MB0686; BCL:0; PCL:0; RULEID:; SRVR:SN1PR11MB0686; X-Microsoft-Exchange-Diagnostics: 1; SN1PR11MB0686; 4:l0zuvjD3T09WYCMIU+VJK2gqHIdYhScwIKQHemC101laZ8P5d4ny/XZf2EeGksUBn7a0etc95JhroR4JlwkCbNRmEGObQyxJfU/hXCKPnSJv6oTzWsXgbLPhaNR2IyYB2kD26V0FhpW6rkfkCb+EB1HkhGtkV6lI7yq/ogsyKqi38uFZlOTcJBG8wtF9AyEa7WbLCbajA3De+dzZCz9nA9aSBRtqUT9sKIaAOrcxuuVBeFYCUVsBA583c7z/vSUx8ZagoE0yphM0D+pbNCizeum0ZNucQ2r0TNgCcMtjR1njvHjY+h7dEbvF+oAeA4m4rT4E9VLHcqkXZ6iDlec8zSuQ4RgamHL7Vrb0CHnKTf6ViwhH46oyqZTnrhhEkhkg X-Forefront-PRVS: 08076ABC99 X-Forefront-Antispam-Report: SFV:NSPM; SFS:(10009020)(6009001)(24454002)(189002)(479174004)(199003)(377454003)(105586002)(42186005)(101416001)(33656002)(230700001)(5001770100001)(122386002)(189998001)(59896002)(64126003)(86362001)(53416004)(106356001)(65816999)(97736004)(99136001)(87266999)(4326007)(4001350100001)(83506001)(5001960100002)(76176999)(81156007)(54356999)(5008740100001)(36756003)(19580395003)(92566002)(50986999)(40100003)(77096005)(69596002)(5004730100002)(2950100001)(87976001)(15975445007)(50466002)(6116002)(47776003)(65806001)(65956001)(1096002)(586003)(80316001)(23746002)(3826002)(85282002)(336755003)(18886065003); DIR:OUT; SFP:1101; SCL:1; SRVR:SN1PR11MB0686; H:decina.attlocal.net; 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; SN1PR11MB0686; 23:L/ib+qITb4+vXcN7QK2bDuua5+/7hTyoDRIjy?= =?Windows-1252?Q?J1PBV4DGcNpCwihJkQYnhQrb7r+8VRJhgRR/nG5uoTqPPYKDQHiVsAqP?= =?Windows-1252?Q?Nti+VOy47Z6HNX+oqIZQIFFpcPvmusvDndY96BODIrLkNjAGCMpa6I2t?= =?Windows-1252?Q?k9RBz+De515iH1sKbRHoDRO8/p4F5p72jgAKJhHvH0m9SAOCCCuREdtq?= =?Windows-1252?Q?3pmhA4aGBYyoPOECDlbcN/M9AO27BZTwujezp85o6fJHRMdwwOpcnLbM?= =?Windows-1252?Q?AsP54uGZ4+ZNcu1YaZT9uJVz6XRCQx3ERI7Bba4Ka6LmJZS+6F972vZG?= =?Windows-1252?Q?kM3/fUD3Nz6mvDGBuQ1FFrcCRtmn0wf0cx3e52+0kEg43GkVCPyJI84y?= =?Windows-1252?Q?DAO82AiFKv9kZZeow5WgF1SfpUVPaGg9qhDiCVNPBo6W6aB1Bc6Mv+ay?= =?Windows-1252?Q?/wHOn/8WstRrPvp6nMTQDWggUh5iLaxg3pjk3bMBepo2M3Fdr5PlGzDM?= =?Windows-1252?Q?Lg4l9kyZtjSPfGdL/8N5U7piHT4wLc3ZN3lKfpMtmqCiLX0tpUjCmYjc?= =?Windows-1252?Q?QBq18I5IUNePPMxVxaCZXtjdoqOv1ceZim7759vuh70LpWtzFANiJx6s?= =?Windows-1252?Q?ttHzwVJcsg1L7zZi67bt55IO6+fibwzX/azNs5bzyJi2AiaXa/bTeDJm?= =?Windows-1252?Q?lpUmC+QQNtmHhG9iTFwcV8+r7VcSSg9QTUH+JdyQZUeNiW3GcDQ8OziJ?= =?Windows-1252?Q?36Aa3jAyzQebgteQdCU3S350n3Ebrh5VoJ4L/bhP0QUhSPwLFagwilGP?= =?Windows-1252?Q?WmZDMJALRX3Y5ow0O5qxeIBR3STpNFEJCLEXC8ADTFSkjSXfY/daeuH0?= =?Windows-1252?Q?DZPKMlo/+F435XCSbo3VuSnUOzN9Mzd7/QcOjAdKXASSs2KonBgXaPTJ?= =?Windows-1252?Q?/Aa0QylbXe7sgKZWH90ig39GU9KolmF0HfszZhDQDSgl8xBKbWLHahSy?= =?Windows-1252?Q?QmjwVCMz/IwGtyqm6DTg5zq4jRTnRBAbwXs5TCQyK1jwh1NIxXPYyZOK?= =?Windows-1252?Q?Ea0O2azD4h+UfcO3o/ibpCEQ0Oom7eJgaXdx5c5qNfkTNdsWpt9py3w2?= =?Windows-1252?Q?PNG3YpX1WtSSKeDLIOzDg99TcWaEZgFwKmbSDuVfI5ltjEuAX75Y+gjI?= =?Windows-1252?Q?XvJCDB3KDHPW7V4jtso8VyAeU6qL6EK+AjcUTN3c39VYwUIX0RRMteS/?= =?Windows-1252?Q?nSXE5CxDbcamysO1XJ2jQr0mWqTTmHCiR2UxRJjST+QWm0PvgqAt59Rf?= =?Windows-1252?Q?dSQL1nSYA9uC5OveCD+2/anDw0IKG1TKbuvPLYWyiD2TfBpMSc6GCY1P?= =?Windows-1252?Q?ArTtNSzrbU7rD0Kn2ZWvTSx1eITe2GWdrRVVVUQfObS5Wu/X3h/WAeCF?= =?Windows-1252?Q?4XJV1VyCP1756zx7OtEZFNwO2JnAZl/Ucm8ABHWYuHXFWV7bCdpckXUo?= =?Windows-1252?Q?gXKxx8x8bk5MiR6MZs7VDoBemjEwt1KBEvK5AhACpTYHa0Hhg=3D=3D?= X-Microsoft-Exchange-Diagnostics: 1; SN1PR11MB0686; 5:diagob3aHMbc/7RPQf6CUZ5G9nRt/jAStZdVDlIWfnCNUljgCRmQgHJBRV+jeqZp4o2h/lK5CG0YWMkn3rVrkdv6GJHUiF84qb3Fzg/mSOo0I62qbiJsUNEkWw0ksn60bzYViVAZyJbS55htg/Y+ug==; 24:R9g6Y6v4CDKVOyzUXWlO51Xk7PhvioGGxaCfofFppZDO+H/ggFO/kHsD9BDYADzdLe0ylVcpvnGku7pNhRV1gMcwQ5slo6J/SY8Jnlvdxmo= SpamDiagnosticOutput: 1:23 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 31 Dec 2015 16:29:15.8309 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: SN1PR11MB0686 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 12/31/15 9:02 AM, Tom Lane wrote: > If you don't want to rebuild the whole database, you can create indexes to > support this by declaring them with COLLATE "C", or the older way is to > declare them with text_pattern_ops as the index opclass. Do you have to do anything special in the query itself for COLLATE "C" to work? I didn't realize the two methods were equivalent. -- 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance