Skip site navigation (1) Skip section navigation (2)

Re: LIKE search and performance

From: Richard Huxton <dev(at)archonet(dot)com>
To: Gregory Stark <stark(at)enterprisedb(dot)com>
Cc: mark(at)mark(dot)mielke(dot)cc, Mark Lewis <mark(dot)lewis(at)mir3(dot)com>,James Mansion <james(at)mansionfamily(dot)plus(dot)com>,Magnus Hagander <magnus(at)hagander(dot)net>,Alexander Staubo <alex(at)purefiction(dot)net>, Andy <frum(at)ar-sd(dot)net>,pgsql-performance(at)postgresql(dot)org
Subject: Re: LIKE search and performance
Date: 2007-05-25 18:13:14
Message-ID: 4657273A.3080005@archonet.com (view raw or flat)
Thread:
Lists: pgsql-performance
Gregory Stark wrote:
> "Richard Huxton" <dev(at)archonet(dot)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.

Sorry - I'm obviously picking my words badly today.

I meant for the "contains" substring match. It gives different (goes 
away and checks...yes) predictions based on string length. So it guesses 
that LIKE '%aaa%' will match more than LIKE '%aaaa%'. Of course, if we 
were matching surnames you and I could say that this is very unlikely, 
but without some big statistics table I guess there's not much more PG 
can do.

For a trailing wildcard LIKE 'aaa%' it can and does as you say convert 
this into something along the lines of (>= 'aaa' AND < 'aab'). Although 
IIRC that depends if your locale allows such (not sure, I don't really 
use non-C/non-English locales enough).

-- 
   Richard Huxton
   Archonet Ltd

In response to

pgsql-performance by date

Next:From: ArnauDate: 2007-05-25 18:16:08
Subject: Re: How PostgreSQL handles multiple DDBB instances?
Previous:From: Dave PirotteDate: 2007-05-25 18:08:52
Subject: Performance problem on 8.2.4, but not 8.2.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group