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@sss.pgh.pa.us>
To: "Samuel J. Sutjiono" <ssutjiono@wc-group.com>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, February 22, 2002 12:58 PM
Subject: Re: [GENERAL] How does Index Scan get used


> "Samuel J. Sutjiono" <ssutjiono@wc-group.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
>