pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Gregory Stark <stark@enterprisedb.com>
To: Richard Huxton <dev@archonet.com>
Cc: mark@mark.mielke.cc
Cc: Mark Lewis <mark.lewis@mir3.com>
Cc: James Mansion <james@mansionfamily.plus.com>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: Alexander Staubo <alex@purefiction.net>
Cc: Andy <frum@ar-sd.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: LIKE search and performance
Date: Fri, 25 May 2007 18:58:13 +0100
Message-ID: <87y7jckcp6.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <46571CEC.7070202@archonet.com>
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>

"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

view thread (29+ messages)  latest in thread

Message-ID: <87y7jckcp6.fsf@oxford.xeocode.com>
Permalink:  ../87y7jckcp6.fsf@oxford.xeocode.com/
Also on:    postgresql.org/message-id/87y7jckcp6.fsf@oxford.xeocode.com

copy link · copy postgr.es

reply

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, dev@archonet.com, mark@mark.mielke.cc, mark.lewis@mir3.com, james@mansionfamily.plus.com, magnus@hagander.net, alex@purefiction.net, frum@ar-sd.net
  Subject: Re: LIKE search and performance
  In-Reply-To: <87y7jckcp6.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