Received: from mail2.uniserve.com (mail2.uniserve.com [204.244.156.10]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2FJb4x50691 for ; Thu, 15 Mar 2001 14:37:04 -0500 (EST) (envelope-from gutz@kalador.com) Received: from van5d12.dial.uniserve.ca ([204.244.163.123] helo=gutz.kalador.com) by mail2.uniserve.com with esmtp (Exim 3.13 #1) id 14ddYU-000Lfo-00; Thu, 15 Mar 2001 11:37:02 -0800 Message-Id: <5.0.2.1.0.20010315113535.0282b750@kalador.com> X-Sender: gutz@kalador.com X-Mailer: QUALCOMM Windows Eudora Version 5.0.2 Date: Thu, 15 Mar 2001 11:42:21 -0800 To: pgsql-sql@postgresql.org From: Gerald Gutierrez Subject: VACUUM kills Index Scans ?! Cc: gutz@colo.kalador.com Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200103/315 X-Sequence-Number: 1979 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=>