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=>
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 |