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

Re: SELECT LIKE 'xxx%' doesn't use index scan

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: dhliu <dh(dot)liu(at)msa(dot)hinet(dot)net>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: SELECT LIKE 'xxx%' doesn't use index scan
Date: 2007-12-19 09:26:39
Message-ID: 162867790712190126k1602dff2t110ecb077c35905a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hello

It's not bug. a LIKE 'some%' is different operation than a = str_const.

if you don't use C locale, then you have to use  varchar_pattern_ops.

please try:

  create index like_index on tab(str varchar_pattern_ops);

don't forget do ANALYZE tab before

Regards
Pavel Stehule

On 19/12/2007, dhliu <dh(dot)liu(at)msa(dot)hinet(dot)net> wrote:
> db =# explain SELECT seq FROM tab WHERE str like 'edt%';
> QUERY PLAN
> -----------------------------------------------------------
> Seq Scan on tab (cost=0.00..6465.55 rows=1 width=4)
> Filter: (str ~~ 'edt%'::text)
> (2 rows)
>
> db=# explain SELECT seq FROM tab WHERE str = 'edta';
> QUERY PLAN
> ------------------------------------------------------------------------------
> Index Scan using tab_pkey on tab (cost=0.00..8.30 rows=1 width=4)
> Index Cond: (str = 'edta'::text)
> (2 rows)
>
>
>
> PostgreSQL 8.2.5 on i586-mandriva-linux-gnu, compiled by GCC
> i586-mandriva-linux-gnu-gcc (GCC) 4.2.2 20070909 (prerelease)
> (4.2.2-0.RC.1mdv2008.0)
>
>
> Thank you.
>
>
> Edward Liu
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

In response to

pgsql-bugs by date

Next:From: Pavel StehuleDate: 2007-12-19 09:29:33
Subject: Re: [BUGS] BUG #3829: Wrong index reporting from pgAdmin III (v1.8.0 rev 6766-6767)
Previous:From: BoonchaiDate: 2007-12-19 07:19:10
Subject: BUG #3829: Wrong index reporting from pgAdmin III (v1.8.0 rev 6766-6767)

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