Re: Query performance over a large proportion of data

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



 

Tom Lane <tgl@xxxxxxxxxxxxx>
Sent by: pgsql-performance-owner@xxxxxxxxxxxxxx
03/10/2009 08:16 PM AST

"Steve McLellan" <smclellan@xxxxxxxxxx> 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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux