Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id ADD0C9FCB8C; Thu, 8 Nov 2007 20:40:31 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 95464-04; Thu, 8 Nov 2007 20:40:27 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id B08A09FCB89; Thu, 8 Nov 2007 20:40:26 -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 lA90eKxv025288; Thu, 8 Nov 2007 19:40:20 -0500 (EST) To: Gregory Stark cc: "Guillaume Smet" , "pgsql-performance" , pgsql-hackers@postgresql.org Subject: Re: Estimation problem with a LIKE clause containing a / In-reply-to: <87prykpc3q.fsf@oxford.xeocode.com> 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> Comments: In-reply-to Gregory Stark message dated "Thu, 08 Nov 2007 23:10:49 +0000" Date: Thu, 08 Nov 2007 19:40:20 -0500 Message-ID: <25287.1194568820@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/123 X-Sequence-Number: 27538 Gregory Stark writes: > Doesn't really strike at the core reason that this is so klugy though. Surely > the "right" thing is to push the concept of open versus closed end-points > through deeper into the estimation logic? No, the right thing is to take the folk who defined "dictionary sort order" out behind the barn and shoot 'em ;-). This has got nothing to do with open/closed endpoints and everything to do with the bizarre sorting rules used by some locales. In particular the reason I want to append a letter is that some locales discriminate against non-letter characters in the first pass of sorting. 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. regards, tom lane