VACUUM kills Index Scans ?!
От | Gerald Gutierrez |
---|---|
Тема | VACUUM kills Index Scans ?! |
Дата | |
Msg-id | 5.0.2.1.0.20010315113535.0282b750@kalador.com обсуждение исходный текст |
Ответы |
Re: VACUUM kills Index Scans ?!
Re: VACUUM kills Index Scans ?! Re: VACUUM kills Index Scans ?! |
Список | 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=>
В списке pgsql-sql по дате отправления: