Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id A7478633B7F for ; Wed, 11 Mar 2009 00:21:12 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 40194-01 for ; Wed, 11 Mar 2009 00:21:11 -0300 (ADT) X-Greylist: delayed 00:05:49.347118 by SQLgrey-1.7.6 Received: from mail-fx0-f168.google.com (mail-fx0-f168.google.com [209.85.220.168]) by mail.postgresql.org (Postfix) with ESMTP id BC5A1633873 for ; Wed, 11 Mar 2009 00:21:02 -0300 (ADT) Received: by fxm12 with SMTP id 12so2706551fxm.19 for ; Tue, 10 Mar 2009 20:21:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:mime-version:sender:reply-to:received :in-reply-to:references:date:x-google-sender-auth:message-id:subject :from:to:content-type; bh=rMTxjc7rTIwStDL+g+67CPHlM+a4Or4OBCRRhX3/CS0=; b=gusf0TvGN/ic45j/MymXB2kDVondyO69CrPKDWifUks0eepbV09SfYr6MNNcigPgZy d4+2LtPmfsmF7etcSAkEYPiS1SookjyTkIHDtfkuY0/uKZoBAcKdGMle69bHr0rYZejv MN7BUw4XikBpqVAEvBx34wMCkQMh/5wtZktio= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=mime-version:sender:reply-to:in-reply-to:references:date :x-google-sender-auth:message-id:subject:from:to:content-type; b=XY5WSnpRc+tJSYHfI1azt2UXT/iTEDMoUz4btXgB4b6rMV2Z5Vxr6FOLdDQni/0Pmp /d/t2yOHLpwTmktKOr4U2IN4guLn3h25CJIKnkcBU99Ja6ZKBmFlMwhql6m6z40XSq0E Irv2DrPiSBeuZqtTzUPwrLTAV/YEoc0A1HMlw= MIME-Version: 1.0 Reply-To: smclellan@mintel.com Received: by 10.223.107.198 with SMTP id c6mr6058244fap.32.1236741661968; Tue, 10 Mar 2009 20:21:01 -0700 (PDT) In-Reply-To: References: Date: Tue, 10 Mar 2009 22:21:01 -0500 X-Google-Sender-Auth: da1d9fe75f4105e9 Message-ID: Subject: Re: Query performance over a large proportion of data From: Steve McLellan To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=001636c5b0ee70c9480464cf5b6c X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200903/90 X-Sequence-Number: 33012 --001636c5b0ee70c9480464cf5b6c Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit > > > > *Tom Lane * > Sent by: pgsql-performance-owner@postgresql.org > 03/10/2009 08:16 PM AST > > "Steve McLellan" writes: > > lc_messages = 'en_US.UTF-8' > > lc_monetary = 'en_US.UTF-8' > > lc_numeric = 'en_US.UTF-8' > > lc_time = 'en_US.UTF-8' > > BTW, aside from the points already made: the above indicates that you > initialized your database in en_US.utf8 locale. This is not necessarily > a good decision from a performance standpoint --- you might be much > better off with C locale, and might even prefer it if you favor > ASCII-order sorting over "dictionary" sorting. utf8 encoding might > create some penalties you don't need too. This all depends on a lot > of factors you didn't mention; maybe you actually need utf8 data, > or maybe your application doesn't do many string comparisons and so > isn't sensitive to the speed of strcoll() anyway. But I've seen it > be a gotcha for people moving from MySQL, which AFAIK doesn't worry > about honoring locale-specific sort order. > > regards, tom lane > > Thanks for the reply. We did intentionally initialize it in UTF-8 locale. We could get away with using C locale in this case, although we try to standardise on UTF-8 in general since we do in other instances require it. The only string comparisons we do are equalities, although it can be the case that we're comparing a large number of rows. We may give it a try and see what kind of performance hit that's giving us. Currently we're trying to get some big easy wins through parameter settings; I imagine we will want to start shaving some more time off queries in the near future. Thanks, Steve --001636c5b0ee70c9480464cf5b6c Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

=A0

Tom Lane <tgl@sss.pgh.pa.us> Sent by: pgsql-performance-owner@postgresql.org
03/10/2009 08:16 PM AST

"Steve McLellan" <smclellan@mintel.com> writes:=
> lc_messages =3D 'en_US.UTF-8'
> lc_monetary =3D 'en_= US.UTF-8'
> lc_numeric =3D 'en_US.UTF-8'
> lc_time = =3D 'en_US.UTF-8'

BT= W, aside from the points already made: the above indicates that you
initialized your database in en_US.utf8 locale. =A0This is not necessarily<= br>a good decision from a performance standpoint --- you might be much
b= etter off with C locale, and might even prefer it if you favor
ASCII-ord= er sorting over "dictionary" sorting. =A0utf8 encoding might
create some penalties you don't need too. =A0This all depends on a lot<= br>of factors you didn't mention; maybe you actually need utf8 data,or maybe your application doesn't do many string comparisons and so isn't sensitive to the speed of strcoll() anyway. =A0But I've seen = it
be a gotcha for people moving from MySQL, which AFAIK doesn't wor= ry
about honoring locale-specific sort order.

    regards, tom lane
Thanks for the reply. We did intentionally i= nitialize it in UTF-8 locale. We could get away with using C locale in this= case, although we try to standardise on UTF-8 in general since we do in ot= her instances require it. The only string comparisons we do are equalities,= although it can be the case that we're comparing a large number of row= s. We may give it a try and see what kind of performance hit that's giv= ing us. Currently we're trying to get some big easy wins through parame= ter settings; I imagine we will want to start shaving some more time off qu= eries in the near future.

Thanks, Steve
--001636c5b0ee70c9480464cf5b6c--