Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id BC2209F99C4; Fri, 9 Nov 2007 05:22:15 -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 46870-02; Fri, 9 Nov 2007 05:22:02 -0400 (AST) Received: from oxford.xeocode.com (unknown [87.127.95.196]) by postgresql.org (Postfix) with ESMTP id BA6969F99BA; Fri, 9 Nov 2007 05:22:04 -0400 (AST) Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com) by oxford.xeocode.com with esmtp (Exim 4.67) (envelope-from ) id 1IqQ3s-0003Nn-CZ; Fri, 09 Nov 2007 09:22:00 +0000 To: "Tom Lane" Cc: "Guillaume Smet" , "pgsql-performance" , Subject: Re: [HACKERS] Estimation problem with a LIKE clause containing a / In-Reply-To: <28300.1194574114@sss.pgh.pa.us> (Tom Lane's message of "Thu\, 08 Nov 2007 21\:08\:34 -0500") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX" 47327) 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> <28300.1194574114@sss.pgh.pa.us> From: Gregory Stark Organization: EnterpriseDB Date: Fri, 09 Nov 2007 09:21:56 +0000 Message-ID: <87hcjvpydn.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/131 X-Sequence-Number: 27546 "Tom Lane" writes: > 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... If I understand correctly what we're talking about it's generating estimates for LIKE 'foo%' using the algorithm which makes sense for C locale which means generating the next range of values which start with 'foo%'. It seems to me the problematic situations is when the most-frequent-values come into play. Being off slightly in the histogram isn't going to generate very inaccurate estimates but including or not a most-frequent-value could throw off the estimate severely. Could we not use the bogus range to calculate the histogram estimate but apply the LIKE pattern directly to the most-frequent-values instead of applying the bogus range? Or would that be too much code re-organization for now? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!