From: Gregory Stark <stark@enterprisedb.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Guillaume Smet <guillaume.smet@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Estimation problem with a LIKE clause containing a /
Date: Fri, 09 Nov 2007 09:21:56 +0000
Message-ID: <87hcjvpydn.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <28300.1194574114@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>
<28300.1194574114@sss.pgh.pa.us>
"Tom Lane" <tgl@sss.pgh.pa.us> 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!
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: stark@enterprisedb.com, tgl@sss.pgh.pa.us, guillaume.smet@gmail.com, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Estimation problem with a LIKE clause containing a /
In-Reply-To: <87hcjvpydn.fsf@oxford.xeocode.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