Re: using index on text type, select with like

From: Arne Weiner <aswr(at)gmx(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: using index on text type, select with like
Date: 2001-09-05 19:39:33
Message-ID: 3B967F75.2A9656C7@gmx.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Browse pgsql-general by date

  From Date Subject
Next Message Gabriel Fernandez 2001-09-06 00:06:53 FOREIGN KEY: MATCH FULL
Previous Message Tomas Berndtsson 2001-09-05 18:44:40 connection failed in threaded application