How does Index Scan get used

On Fri, 22 Feb 2002, Samuel J. Sutjiono wrote:

I tried an Index Scan but I kept on getting Seq Scan. Do you know why ? What do I need to do to get my query to use index scan (I tried to turn it on by doing SET ENABLE_indexscan = on; but it didn't work)

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=0.00..22.50 rows=10 width=12)

Did you do a vacuum analyze after creating the index?

No, I didn't.

----- Original Message -----
From: "Holger Marzen" <holger@marzen.de>
To: "Samuel J. Sutjiono" <ssutjiono@wc-group.com>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, February 22, 2002 10:25 AM
Subject: Re: [GENERAL] How does Index Scan get used

On Fri, 22 Feb 2002, Samuel J. Sutjiono wrote:

I tried an Index Scan but I kept on getting Seq Scan. Do you know why ?

What do I need to do to get my query to use index scan (I tried to turn it
on by doing SET ENABLE_indexscan = on; but it didn't work)

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');

First of all when you create an index u must specify what kind of ops will use
that index, in this case create index test_index on test (test_col , text_ops)

Second, a index only will be used if there is an advantage on using it, in
this case, u have 3 unique values, unless random-page cost is lower than 3,
planner would not use it.
Third of all, if u want to learn how to use indexes, try numeric values first,
because indexing texts is a bit more difficult.

"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#AEN16601

regards, tom lane

On Fri, 22 Feb 2002, Samuel J. Sutjiono wrote:

[Please answer below cited parts.]

No, I didn't.

----- Original Message -----
From: "Holger Marzen" <holger@marzen.de>
To: "Samuel J. Sutjiono" <ssutjiono@wc-group.com>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, February 22, 2002 10:25 AM
Subject: Re: [GENERAL] How does Index Scan get used

On Fri, 22 Feb 2002, Samuel J. Sutjiono wrote:

I tried an Index Scan but I kept on getting Seq Scan. Do you know why ?

What do I need to do to get my query to use index scan (I tried to turn it
on by doing SET ENABLE_indexscan = on; but it didn't work)

explain select * from test where test_col like 'abc/%';

NOTICE: QUERY PLAN:

Seq Scan on test (cost=0.00..22.50 rows=10 width=12)

Did you do a vacuum analyze after creating the index?

If you don't do a "vacuum analyze" for a table after creating an index
then Postgres has not all the informations to optimize a query. "vaccum
analyze" is recommended on a regular basis. I do it every night.

--
PGP/GPG Key-ID:
http://blackhole.pca.dfn.de:11371/pks/lookup?op=get&amp;search=0xB5A1AFE1

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