Thread: LIKE query on indexes
hi,
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?
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?
On Tue, 2006-02-21 at 09:57, Ibrahim Tekin wrote: > hi, > 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? This is an artifact of how PostgreSQL handles locales other than ASCII. If you want such a query to use an index, you need to back up your database, and re-initdb with --locale=C as an argument. Note that you then will NOT get locale specific matching and sorting.
Scott Marlowe wrote: > On Tue, 2006-02-21 at 09:57, Ibrahim Tekin wrote: > > hi, > > 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? > > This is an artifact of how PostgreSQL handles locales other than ASCII. > > If you want such a query to use an index, you need to back up your > database, and re-initdb with --locale=C as an argument. ... or you can choose to create an index with the text_pattern_ops operator class, which would be used in a LIKE constraint regardless of locale. http://www.postgresql.org/docs/8.1/static/indexes-opclass.html -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Tue, 2006-02-21 at 10:34, Alvaro Herrera wrote: > Scott Marlowe wrote: > > On Tue, 2006-02-21 at 09:57, Ibrahim Tekin wrote: > > > hi, > > > 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? > > > > This is an artifact of how PostgreSQL handles locales other than ASCII. > > > > If you want such a query to use an index, you need to back up your > > database, and re-initdb with --locale=C as an argument. > > ... or you can choose to create an index with the text_pattern_ops > operator class, which would be used in a LIKE constraint regardless of > locale. > > http://www.postgresql.org/docs/8.1/static/indexes-opclass.html Good point. I tend to view the world from the perspective of the 7.4 and before user...
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@mielke.cc / markm@ncf.ca / markm@nortel.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/
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, ..'
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@mark.mielke.cc <mark@mark.mielke.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@mielke.cc / markm@ncf.ca / markm@nortel.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/
this trick did the job.
thanks.
thanks.
On 2/21/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
Scott Marlowe wrote:
> On Tue, 2006-02-21 at 09:57, Ibrahim Tekin wrote:
> > hi,
> > 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?
>
> This is an artifact of how PostgreSQL handles locales other than ASCII.
>
> If you want such a query to use an index, you need to back up your
> database, and re-initdb with --locale=C as an argument.
... or you can choose to create an index with the text_pattern_ops
operator class, which would be used in a LIKE constraint regardless of
locale.
http://www.postgresql.org/docs/8.1/static/indexes-opclass.html
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Hi,
Can this technique work with case insensitive ILIKE?
It didn't seem to use the index when I used ILIKE instead of LIKE.
Thanks,
____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
http://www.clickspace.com
____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
http://www.clickspace.com
On Feb 21, 2006, at 1:28 PM, Ibrahim Tekin wrote:
this trick did the job.
thanks.On 2/21/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:Scott Marlowe wrote:
> On Tue, 2006-02-21 at 09:57, Ibrahim Tekin wrote:
> > hi,
> > 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?
>
> This is an artifact of how PostgreSQL handles locales other than ASCII.
>
> If you want such a query to use an index, you need to back up your
> database, and re-initdb with --locale=C as an argument.
... or you can choose to create an index with the text_pattern_ops
operator class, which would be used in a LIKE constraint regardless of
locale.
http://www.postgresql.org/docs/8.1/static/indexes-opclass.html
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Attachment
hi,
i ran a query with ILIKE but it doesn't use the index.
but i tried following method, and it worked. there is 3 extra lower() overhead but i don't think it will effect the performance.
CREATE INDEX index_name ON mytable (lower(column) varchar_pattern_ops);
SELECT * FROM mytable WHERE lower(column) LIKE lower('beginswith%')
if insert operations are high in database. you use only this index to search case sensitive.
say you want this:
SELECT * FROM mytable WHERE column LIKE 'beGinsWith%'
write this:
SELECT * FROM mytable WHERE lower(column) LIKE lower('beGinsWith%') AND column LIKE 'beGinsWith%'
than query planner will search on index, than scan the resulting bitmap heap.
i ran a query with ILIKE but it doesn't use the index.
but i tried following method, and it worked. there is 3 extra lower() overhead but i don't think it will effect the performance.
CREATE INDEX index_name ON mytable (lower(column) varchar_pattern_ops);
SELECT * FROM mytable WHERE lower(column) LIKE lower('beginswith%')
if insert operations are high in database. you use only this index to search case sensitive.
say you want this:
SELECT * FROM mytable WHERE column LIKE 'beGinsWith%'
write this:
SELECT * FROM mytable WHERE lower(column) LIKE lower('beGinsWith%') AND column LIKE 'beGinsWith%'
than query planner will search on index, than scan the resulting bitmap heap.
On 2/22/06, Brendan Duddridge <brendan@clickspace.com> wrote:
Hi,Can this technique work with case insensitive ILIKE?It didn't seem to use the index when I used ILIKE instead of LIKE.Thanks,
____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
http://www.clickspace.comOn Feb 21, 2006, at 1:28 PM, Ibrahim Tekin wrote:this trick did the job.
thanks.On 2/21/06, Alvaro Herrera < alvherre@commandprompt.com> wrote:Scott Marlowe wrote:
> On Tue, 2006-02-21 at 09:57, Ibrahim Tekin wrote:
> > hi,
> > 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?
>
> This is an artifact of how PostgreSQL handles locales other than ASCII.
>
> If you want such a query to use an index, you need to back up your
> database, and re-initdb with --locale=C as an argument.
... or you can choose to create an index with the text_pattern_ops
operator class, which would be used in a LIKE constraint regardless of
locale.
http://www.postgresql.org/docs/8.1/static/indexes-opclass.html
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.