From: Steve McLellan <smclellan@mintel.com>
To: pgsql-performance@postgresql.org
Subject: Re: Query performance over a large proportion of data
Date: Tue, 10 Mar 2009 22:21:01 -0500
Message-ID: <cfca83d70903102021t16bfed28l4343352ec62e56da@mail.gmail.com> (raw)
In-Reply-To: <OF74455290.6647AE91-ON86257576.001167E0-86257576.001167FE@MintelChicago.usdmm.com>
References: <OF74455290.6647AE91-ON86257576.001167E0-86257576.001167FE@MintelChicago.usdmm.com>
>
>
>
> *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 = '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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: smclellan@mintel.com
Subject: Re: Query performance over a large proportion of data
In-Reply-To: <cfca83d70903102021t16bfed28l4343352ec62e56da@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox