Re: How does Index Scan get used

Tom,

I still haven't been able to use index scan on my LIKE query.

- I am using BTREE index. Am I supposed to use RTREE for LIKE to use index ?
- I am not using a locale.

Please let me know what other settings(parameters) that I need to check to
make the query optimizer use Index Scan.
I would really appreciate your help.

Best Regards,
Sam

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Samuel J. Sutjiono" <ssutjiono(at)wc-group(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Friday, February 22, 2002 12:58 PM
Subject: Re: [GENERAL] How does Index Scan get used

> "Samuel J. Sutjiono" <ssutjiono(at)wc-group(dot)com> writes:
> > create table test (test_col text);
> > create index test_index on test (test_col);
> > insert into test values ('abc.xyz');
> > insert into test values ('abcxyz');
> > insert into test values ('abc/xyz');
>
> > explain select * from test where test_col like 'abc/%';
>
> > NOTICE: QUERY PLAN:
>
> > Seq Scan on test (cost=3D0.00..22.50 rows=3D10 width=3D12)
>
> I did the above and got:
>
> regression=# explain select * from test where test_col like 'abc/%';
> NOTICE: QUERY PLAN:
>
> Index Scan using test_index on test (cost=0.00..17.07 rows=5 width=32)
>
> Perhaps you are using a locale that prevents LIKE from being optimized
> into an indexscan? See
>
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/charset.html#AEN
16601
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>