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

Re: LIKE search and performance

From: Richard Huxton <dev(at)archonet(dot)com>
To: mark(at)mark(dot)mielke(dot)cc
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 08:13:25
Message-ID: 46569AA5.10205@archonet.com (view raw or flat)
Thread:
Lists: pgsql-performance
mark(at)mark(dot)mielke(dot)cc wrote:
>> And since it's basically impossible to know the selectivity of this kind
>> of where condition, I doubt the planner would ever realistically want to
>> choose that plan anyway because of its poor worst-case behavior.
> 
> What is a real life example where an intelligent and researched
> database application would issue a like or ilike query as their
> primary condition in a situation where they expected very high
> selectivity?
> 
> Avoiding a poor worst-case behaviour for a worst-case behaviour that
> won't happen doesn't seem practical.

But if you are also filtering on e.g. date, and that has an index with 
good selectivity, you're never going to use the text index anyway are 
you? If you've only got a dozen rows to check against, might as well 
just read them in.

The only time it's worth considering the behaviour at all is *if* the 
worst-case is possible.

-- 
   Richard Huxton
   Archonet Ltd

In response to

Responses

pgsql-performance by date

Next:From: Bastian VoigtDate: 2007-05-25 08:29:30
Subject: Performance Problem with Vacuum of bytea table (PG 8.0.13)
Previous:From: PFCDate: 2007-05-24 22:09:15
Subject: Re: LIKE search and performance

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