Re: using index on text type, select with like

If your SELECT has a search patter beginning with an '%' then
postgres has to check every row anyway. So why using an Index then?
Using in index in this case would even slow down the query, because the
index must be loaded too.

Arne.

Szabo Zoltan wrote:
>
> Hi,
>
> The question is: how can I use index on text type?
>
> I have the following:
> create table test_t ( text text);
> create index test_t_idx on test_t (text);
> ... some inserts ...
>
> select count(*) from test_t; => 3
>
> and the explains:
>
> db=> EXPLAIN select * from test_t where text like 'a%';
> NOTICE: QUERY PLAN:
> Index Scan using test_t_idx on test_t (cost=0.00..8.14 rows=10
> width=12)
> EXPLAIN
>
> db=> EXPLAIN select * from test_t where text like 'a';
> NOTICE: QUERY PLAN:
> Index Scan using test_t_idx on test_t (cost=0.00..8.14 rows=10
> width=12)
> EXPLAIN
>
> db=> EXPLAIN select * from test_t where text like '%a%';
> NOTICE: QUERY PLAN:
> Seq Scan on test_t (cost=0.00..22.50 rows=200 width=12)
> EXPLAIN
>
> db=> EXPLAIN select * from test_t where text like '%a';
> NOTICE: QUERY PLAN:
> Seq Scan on test_t (cost=0.00..22.50 rows=40 width=12)
> EXPLAIN
> --------
> As it shows, index used only if I match from the begining of data.
>
> Thx