VACUUM kills Index Scans ?!

From: Gerald Gutierrez <gutz(at)kalador(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Cc: gutz(at)colo(dot)kalador(dot)com
Subject: VACUUM kills Index Scans ?!
Date: 2001-03-15 19:42:21
Message-ID: 5.0.2.1.0.20010315113535.0282b750@kalador.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


I'm confused over two question involving PostgreSQL index scans. I'm using
Linux Mandrake 7.2 on Intel, and PostgreSQL 7.0.3. If someone can explain
what's going on, I'd greatly appreciate it.

---------------------------------------------

1) When I create a empty table, and then immediate create an index on a
column, I can get /index scans/ when searching on that column. But when I
then run VACUUM, the same search becomes a /sequential scan/. Shouldn't it
still be an index scan? What's going on here?

test1=> create table t1 (a varchar(64), b int);
CREATE
test1=> create index t1_a_ndx on t1 (a);
CREATE
test1=> explain select * from t1 where a='asd';
NOTICE: QUERY PLAN:

Index Scan using t1_a_ndx on t1 (cost=0.00..8.14 rows=10 width=16)

EXPLAIN
test1=> vacuum;
NOTICE: Skipping "pg_type" --- only table owner can VACUUM it (a bunch of
these)
VACUUM
test1=> explain select * from t1 where a='asd';
NOTICE: QUERY PLAN:

Seq Scan on t1 (cost=0.00..0.00 rows=1 width=16)

EXPLAIN
test1=>

---------------------------------------------

2) If I already have some data in a table and I create an index on a
column, why doesn't subsequent searches then change from sequential scans
to index scans?

test1=> create table t2 (a varchar(64), b int);
CREATE
test1=> insert into t2 values ('a', 1);
INSERT 41255 1
test1=> insert into t2 values ('b', 2);
INSERT 41256 1
test1=> insert into t2 values ('c', 3);
INSERT 41257 1
test1=> explain select * from t2 where a='a';
NOTICE: QUERY PLAN:

Seq Scan on t2 (cost=0.00..22.50 rows=10 width=16)

EXPLAIN
test1=> create index t2_a_ndx on t2 (a);
CREATE
test1=> explain select * from t2 where a='a';
NOTICE: QUERY PLAN:

Seq Scan on t2 (cost=0.00..1.04 rows=1 width=16)

EXPLAIN
test1=>

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Gerald Gutierrez 2001-03-15 20:03:51 Re: VACUUM kills Index Scans ?!
Previous Message Tom Lane 2001-03-15 17:02:37 Re: List Concatination