Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 9326D9FCB8E; Thu, 8 Nov 2007 22:08:53 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 92084-01; Thu, 8 Nov 2007 22:08:34 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 02BAF9FCB8F; Thu, 8 Nov 2007 22:08:39 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id lA928Yd5028301; Thu, 8 Nov 2007 21:08:35 -0500 (EST) To: Gregory Stark , "Guillaume Smet" , "pgsql-performance" , pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Estimation problem with a LIKE clause containing a / In-reply-to: <25287.1194568820@sss.pgh.pa.us> References: <1d4e0c10711070453q214f089cpd144cbb5193d3c4f@mail.gmail.com> <17767.1194453292@sss.pgh.pa.us> <11467.1194477248@sss.pgh.pa.us> <1d4e0c10711080208n6c9875b4tf5882da3ced18532@mail.gmail.com> <27405.1194542548@sss.pgh.pa.us> <87prykpc3q.fsf@oxford.xeocode.com> <25287.1194568820@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Thu, 08 Nov 2007 19:40:20 -0500" Date: Thu, 08 Nov 2007 21:08:34 -0500 Message-ID: <28300.1194574114@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/125 X-Sequence-Number: 27540 I wrote: > I did do some experimentation and found that among the ASCII characters > (ie, codes 32-126), nearly all the non-C locales on my Fedora machine > sort Z last and z next-to-last or vice versa. Most of the remainder > sort digits last and z or Z as the last non-digit character. Since Z is > not that close to the end of the sort order in C locale, however, z > seems the best bet. With still further experimentation, it seems that doesn't work very well, because the locales that sort digits last also seem not to discriminate against digits in their first pass. What did seem to work was: * Determine which of the strings "Z", "z", "y", "9" is seen as largest by strcoll(). * Append this string to the given input. * Search (using the CVS-HEAD make_greater_string logic) for a string greater than that. This rule works for all the locales I have installed ... but I don't have any Far Eastern locales installed. Also, my test cases are only covering ASCII characters, and I believe many locales have some non-ASCII letters that sort after 'Z'. I'm not sure how hard we need to try to cover those corner cases, though. It is ultimately only an estimate... regards, tom lane