Обсуждение: 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
indexscan (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');
> >
> > 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?
>
>

 
> >
> > 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&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
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>