Re: why is index not used?

Marcin Krol <mrkafk@gmail.com> writes:
> booktown=# explain analyze select * from auth where first_name like 'a11%';
>                                                 QUERY PLAN
> --------------------------------------------------------------------------------------------------------
>   Seq Scan on auth  (cost=0.00..56796.68 rows=1 width=42) (actual
> time=0.091..983.665 rows=1111 loops=1)
>     Filter: ((first_name)::text ~~ 'a11%'::text)
>   Total runtime: 986.314 ms
> (3 rows)

> FAQ says that in order to use index, LIKE statements cannot begin with
> %, so I should be fine?

The other point is that in non-C locales, a standard varchar index isn't
usable for LIKE (the sort order is usually wrong).  You can re-initdb
in C locale or create a varchar_pattern_ops index.

            regards, tom lane