Thread: 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)
>
> Another question:
> Does Regex (~*) or like use table scan ?

It may, but IIRC only if you're running in 'C' locale (the
rule being used to make it indexable doesn't necessarily
work in other locales).

On Fri, 2002-02-22 at 14:46, 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)

PostgreSQL thinks there are only 10 rows in the table.  Therefore it
will not choose an index scan because for so few rows it is bound to be
more expensive than a sequential scan.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
    "The LORD bless thee, and keep thee; The LORD make his     face shine upon thee, and be gracious unto thee; The
LORD lift up his countenance upon thee, and give thee      peace."              Numbers 6:24-26 
 

On Fri, 2002-02-22 at 18:08, Oliver Elphick wrote:
> PostgreSQL thinks there are only 10 rows in the table.  Therefore it
> will not choose an index scan because for so few rows it is bound to be
> more expensive than a sequential scan.

But then I saw Tom's mail saying that it does use the index.  How is
that?  Surely these few values would all be brought in in one single
disk read?

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
    "The LORD bless thee, and keep thee; The LORD make his     face shine upon thee, and be gracious unto thee; The
LORD lift up his countenance upon thee, and give thee      peace."              Numbers 6:24-26 
 

LIKE works fine on my system (see regex notes below):

fbax=# create table test (test_col text);
CREATE
fbax=# create index test_index on test (test_col);
CREATE
fbax=# insert into test values ('abc.xyz');
INSERT 6189442 1
fbax=# insert into test values ('abcxyz');
INSERT 6189443 1
fbax=# insert into test values ('abc/xyz');
INSERT 6189444 1
fbax=# explain select * from test where test_col like 'abc/%';
NOTICE:  QUERY PLAN:

Index Scan using test_index on test  (cost=0.00..8.14 rows=10 width=12)

EXPLAIN
fbax=# select version();                             version
-------------------------------------------------------------------PostgreSQL 7.1 on i386-unknown-openbsd2.9, compiled
byGCC 2.95.3
 
(1 row)
fbax=# create index test_lower_index on test (lower(test_col));
CREATE
fbax=# explain select * from test where lower(test_col) ~ '^abc';
NOTICE:  QUERY PLAN:

Seq Scan on test  (cost=0.00..1.04 rows=1 width=12)

EXPLAIN
fbax=# insert into test ( select surname from chl_name );
INSERT 0 419
fbax=# vacuum analyse test;
VACUUM
fbax=# explain select * from test where lower(test_col) ~ '^abc';
NOTICE:  QUERY PLAN:

Index Scan using test_lower_index on test  (cost=0.00..4.07 rows=4 width=12)

EXPLAIN
=====     =====     =====     =====     =====
regex ~* won't use index, because of the case-insensitive stuff.
The above example only works when you anchor search to beginning of field
using '^'.

Frank


At 09:46 AM 2/22/02 -0500, 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)
> 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/%';
>QUERY PLAN:  
>Seq Scan   (cost=0.00..22.50 rows=10 width=12)
>Another question: Does
>Regex (~*) or like use table scan  ?   Thanks, Sam 
>_________________________________________________
> Expand your  wireless world with Arkdom PLUS
> http://www.arkdom.com/
> 

Oliver Elphick <olly@lfix.co.uk> writes:
> On Fri, 2002-02-22 at 18:08, Oliver Elphick wrote:
>> PostgreSQL thinks there are only 10 rows in the table.  Therefore it
>> will not choose an index scan because for so few rows it is bound to be
>> more expensive than a sequential scan.

> But then I saw Tom's mail saying that it does use the index.  How is
> that?  Surely these few values would all be brought in in one single
> disk read?

Well, there are two different issues here.  One is whether the query
*can* use the index --- that depends on your locale, as I pointed out,
and also on the details of the LIKE or regex pattern being used, as
Frank Bax points out nearby.  The other issue is whether the planner
*will choose to* use the index, when it has a choice.

Oliver is quite correct that the planner would not choose to use the
index on such a small table --- if it knows the table is small.  But
in these dummy examples that we're discussing, we just created the table
and index and then put a few values in (the order is important BTW),
and we never vacuumed.  In this scenario the table size estimates in
pg_class have never been changed from the initial dummy values that
CREATE TABLE puts in --- and those dummy values are deliberately set
large enough to allow index scans.  (10 pages / 1000 rows, IIRC.)
We don't initialize the size estimates to zero, because if we did,
the planner would make totally foolish choices for never-vacuumed
tables ... which could actually contain lots of data.

VACUUM the test table, and it'll stop using the index, unless you put a
lot more test data in than we've used in these examples.

Oh BTW: the "10 rows" Oliver was wondering about are not the number of
rows the planner thinks are in the table; they're the estimated number
of result rows.  Since there's no VACUUM ANALYZE stats available either,
this is just a default selectivity estimate for the match clause (0.01)
times the initial dummy reltuples value (1000).  Not much content in it
at all, eh?
        regards, tom lane

On Sat, 2002-02-23 at 00:11, Tom Lane wrote:
...
> Oh BTW: the "10 rows" Oliver was wondering about are not the number of
> rows the planner thinks are in the table; they're the estimated number
> of result rows.  Since there's no VACUUM ANALYZE stats available either,
> this is just a default selectivity estimate for the match clause (0.01)
> times the initial dummy reltuples value (1000).  Not much content in it
> at all, eh?

Thanks, Tom.  That was most informative.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
    "The LORD bless thee, and keep thee; The LORD make his     face shine upon thee, and be gracious unto thee; The
LORD lift up his countenance upon thee, and give thee      peace."              Numbers 6:24-26