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

Re: LIKE query on indexes

From: mark(at)mark(dot)mielke(dot)cc
To: Ibrahim Tekin <itekin(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: LIKE query on indexes
Date: 2006-02-21 17:40:48
Message-ID: 20060221174048.GA8108@mark.mielke.cc (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Feb 21, 2006 at 05:57:12PM +0200, Ibrahim Tekin wrote:
> i have btree index on a text type field. i want see rows which starts with
> certain characters on that field. so i write a query like this:
>     SELECT * FROM mytable WHERE myfield LIKE 'john%'
> since this condition is from start of the field, query planner should use
> index to find such elements but explain command shows me it will do a
> sequential scan.
> is this lack of a feature or i am wrong somewhere?

Is the query fast enough? How big is your table? What does explain
analyze select tell you?

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

Responses

pgsql-performance by date

Next:From: George WoodringDate: 2006-02-21 20:12:03
Subject: Help with nested loop left join performance
Previous:From: Scott MarloweDate: 2006-02-21 16:42:24
Subject: Re: LIKE query on indexes

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