Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 31E629FB6B6 for ; Tue, 8 May 2007 11:59:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 38496-04 for ; Tue, 8 May 2007 11:59:31 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.183]) by postgresql.org (Postfix) with ESMTP id A44909FB539 for ; Tue, 8 May 2007 11:59:31 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id a73so1552814pye for ; Tue, 08 May 2007 07:59:31 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; b=FwFYfMHqhuBQFvqgnIeU0K1LTSnOZygMGIthIoPETVnbIfo4hq6mKApteu4jwhMualpp050THlqZTyqBSoqg3MYyoLdbizv6G8AJgc/ZJ0FmcjEzcxx6ljfAxD3/Ad5E351UUfYpjzQVZWbdQpnOEVTpgawzm/+crehGpupbT0Y= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; b=dlwMmWV6AIQTQMRenZf5s8ebdL9klQwMMQSmNp4q08he02wevvv4CuLaAZJYM5avYMAOsYHRO6qdKOsUKWurMKd/ijVj/8z4u764OCWmPVLU3JIjzo3jyMvncSZgNbMV1OS26vVAiNbDkp4yoBT9hKzmUR8jZCAdzAl1t9YznyI= Received: by 10.65.250.11 with SMTP id c11mr12263212qbs.1178636370836; Tue, 08 May 2007 07:59:30 -0700 (PDT) Received: by 10.65.240.15 with HTTP; Tue, 8 May 2007 07:59:30 -0700 (PDT) Message-ID: <88daf38c0705080759o2e5771c3qdf1b34baa581fc53@mail.gmail.com> Date: Tue, 8 May 2007 16:59:30 +0200 From: "Alexander Staubo" To: "Tom Lane" Subject: Re: specific query (not all) on Pg8 MUCH slower than Pg7 Cc: "Susan Russo" , pgsql-performance@postgresql.org, harvsys@morgan.harvard.edu In-Reply-To: <1160.1178635705@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <200705081418.l48EIY8V026360@larrys.harvard.edu> <1160.1178635705@sss.pgh.pa.us> X-Google-Sender-Auth: 820b9eea6fc241de X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/121 X-Sequence-Number: 24511 On 5/8/07, Tom Lane wrote: > You're not getting the indexscan optimization of the LIKE clause, which > is most likely due to having initdb'd the 8.1 installation in something > other than C locale. You can either redo the initdb in C locale (which > might be a good move to fix other inconsistencies from the 7.3 behavior > you're used to) or create a varchar_pattern_ops index on the column(s) > you're using LIKE with. Given the performance implications of setting the wrong locale, and the high probability of accidentally doing this (I run my shells with LANG=en_US.UTF-8, so all my databases have inherited this locale), why is there no support for changing the database locale after the fact? # alter database test set lc_collate = 'C'; ERROR: parameter "lc_collate" cannot be changed Alexander.