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

Re: LIKE search and performance

From: mark(at)mark(dot)mielke(dot)cc
To: James Mansion <james(at)mansionfamily(dot)plus(dot)com>
Cc: Mark Lewis <mark(dot)lewis(at)mir3(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-06-07 03:33:52
Message-ID: 20070607033352.GA2773@mark.mielke.cc (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Jun 06, 2007 at 11:23:13PM +0100, James Mansion wrote:
> mark(at)mark(dot)mielke(dot)cc wrote:
> >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?
> In my case the canonical example is to search against textual keys
> where the search is performed automatically if the user hs typed
> enough data and paused.  In almost all cases the '%' trails, and I'm
> looking for 'starts with' in effect.  usually the search will have a
> specified upper number of returned rows, if that's an available
> facility.  I realise in this case that matching against the index
> does not allow the match count unless we check MVCC as we go, but I
> don't see why another thread can't be doing that.

I believe PostgreSQL already considers using the index for "starts
with", so this wasn't part of the discussion for me. Sorry that this
wasn't clear.

Cheers,
mark

-- 
mark(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)com     __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
                       and in the darkness bind them...

                           http://mark.mielke.cc/


In response to

pgsql-performance by date

Next:From: davidDate: 2007-06-07 05:22:26
Subject: Re: Thousands of tables versus on table?
Previous:From: Michael GlaesemannDate: 2007-06-07 00:14:17
Subject: Re: Weird 8.2.4 performance

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