Re: 'like' refuses to use an index??? - Mailing list pgsql-general

Nah...
This is a different story - for teh index to be useful, the *beginning*
of your search string must be known.
So "like '00423%" and "~ '^00423'" should both work, but "like '%423'"
and "~ '00423'" both won't - it's like searching a telephone book for
somebody, whose last name ends with "erry" (as opposed to begins with
"Perr").

Dima


Keith C. Perry wrote:

>I wanted to know this too because I notice that using like with wildcards
>appears to be similar to a regular expression in that the index is not used.
>This is what I have...
>
>ethernet=# select version();
>                               version
>----------------------------------------------------------------------
> PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
>(1 row)
>
>ethernet=# \d vendors
>           Table "public.vendors"
> Column  |         Type          | Modifiers
>---------+-----------------------+-----------
> header  | character(6)          |
> company | character varying(80) |
>Indexes:
>    "vender_id_idx" btree (header)
>
>
>ethernet=# explain select * from vendors where header like '000423';
>                                   QUERY PLAN
>---------------------------------------------------------------------------------
> Index Scan using vender_id_idx on vendors  (cost=0.00..113.26 rows=36 width=68)
>   Index Cond: (header = '000423'::bpchar)
>   Filter: (header ~~ '000423'::text)
>(3 rows)
>
>
>Ok, that made sense-
>
>ethernet=# explain select * from vendors where header like '%000423%';
>                        QUERY PLAN
>----------------------------------------------------------
> Seq Scan on vendors  (cost=0.00..151.15 rows=3 width=68)
>   Filter: (header ~~ '%000423%'::text)
>(2 rows)
>
>This didn't make sense until I did...
>
>ethernet=# explain select * from vendors where header like '0004%';
>                                   QUERY PLAN
>---------------------------------------------------------------------------------
> Index Scan using vender_id_idx on vendors  (cost=0.00..113.26 rows=36 width=68)
>   Index Cond: ((header >= '0004'::bpchar) AND (header < '0005'::bpchar))
>   Filter: (header ~~ '0004%'::text)
>(3 rows)
>
>which again made sense because of the header's size but both-
>
>ethernet=# explain select * from vendors where header ~* '0004';
>                        QUERY PLAN
>-----------------------------------------------------------
> Seq Scan on vendors  (cost=0.00..151.15 rows=58 width=68)
>   Filter: (header ~* '0004'::text)
>(2 rows)
>
>ethernet=# explain select * from vendors where header ~* '000423';
>                        QUERY PLAN
>----------------------------------------------------------
> Seq Scan on vendors  (cost=0.00..151.15 rows=3 width=68)
>   Filter: (header ~* '000423'::text)
>(2 rows)
>
>are sequentially scanned which means that regex's do not use indexes.  Is that
>right also?
>
>
>