From: Gregory Stark <stark@enterprisedb.com>
To: "Richard Huxton" <dev@archonet.com>
Cc: <mark@mark.mielke.cc>, "Mark Lewis" <mark.lewis@mir3.com>,
	"James Mansion" <james@mansionfamily.plus.com>,
	"Magnus Hagander" <magnus@hagander.net>,
	"Alexander Staubo" <alex@purefiction.net>,
	"Andy" <frum@ar-sd.net>, <pgsql-performance@postgresql.org>
Subject: Re: LIKE search and performance
In-Reply-To: <46571CEC.7070202@archonet.com> (Richard Huxton's message of
	"Fri, 25 May 2007 18:29:16 +0100")
Organization: EnterpriseDB
References: <009501c79d4c$445428f0$0b00a8c0@mpsro.dom>
	<88daf38c0705230905u1a981102k691b47bd36011a80@mail.gmail.com>
	<4655DE75.4080506@mansionfamily.plus.com>
	<4655E64B.5050503@hagander.net>
	<4655FB8A.1040707@mansionfamily.plus.com>
	<1180040560.31471.262.camel@archimedes>
	<20070524215450.GA3858@mark.mielke.cc> <46569AA5.10205@archonet.com>
	<20070525141630.GA12162@mark.mielke.cc> <4657023A.20209@archonet.com>
	<20070525165633.GA13579@mark.mielke.cc>
	<46571CEC.7070202@archonet.com>
Date: Fri, 25 May 2007 18:58:13 +0100
Message-ID: <87y7jckcp6.fsf@oxford.xeocode.com>
User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.4 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"Richard Huxton" <dev@archonet.com> writes:

> Now you and I can look at a substring and probably make a good guess how common
> it is (assuming we know the targets are British surnames or Japanese towns). PG
> needs one number - or rather, it picks one number for each length of
> search-string (afaik).

I don't think that's true. Postgres calculates the lower and upper bound
implied by the search pattern and then uses the histogram to estimate how
selective that range is. It's sometimes surprisingly good but obviously it's
not perfect.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com