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

Re: LIKE query on indexes

From: "Ibrahim Tekin" <itekin(at)gmail(dot)com>
To: "mark(at)mark(dot)mielke(dot)cc" <mark(at)mark(dot)mielke(dot)cc>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: LIKE query on indexes
Date: 2006-02-21 20:12:17
Message-ID: e4dcba670602211212i523ad54bh404bc1097f90ef6f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
my database encoding is unicode.
i have two table, one is 3.64gb on hdd and has 2.2 million records. it takes
140 secs to run on my AMD Turion 64 M 800MHz/1GB laptop.
second table is 1.2gb, 220000 records, and takes 56 secs to run.

explain says 'Seq Scan on mytable, ..'

On 2/21/06, mark(at)mark(dot)mielke(dot)cc <mark(at)mark(dot)mielke(dot)cc> wrote:
>
> 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

pgsql-performance by date

Next:From: Ibrahim TekinDate: 2006-02-21 20:28:09
Subject: Re: LIKE query on indexes
Previous:From: George WoodringDate: 2006-02-21 20:12:03
Subject: Help with nested loop left join performance

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