As additional information that I neglected to include in the first message,
after both tests, the indices clearly still exist and can be seed in the
following commands:
\d t1
\d t2
\di
\d t1_a_ndx
\d t2_a_ndx
The output shows what's expected, e.g:
test1=> \di
List of relations
Name | Type | Owner
----------+-------+-------
t1_a_ndx | index | gutz
t2_a_ndx | index | gutz
(1 row)
test1=> \d t2_a_ndx
Index "t2_a_ndx"
Attribute | Type
-----------+-----------
a | varchar()
btree
At 11:42 AM 3/15/2001 -0800, Gerald Gutierrez wrote:
>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=>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
