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 f2FJwWx53442 for ; Thu, 15 Mar 2001 14:58:32 -0500 (EST) (envelope-from gutz@kalador.com) Received: from van1d30.dial.uniserve.ca ([204.244.158.109] helo=gutz.kalador.com) by mail2.uniserve.com with esmtp (Exim 3.13 #1) id 14ddtF-000NG0-00; Thu, 15 Mar 2001 11:58:30 -0800 Message-Id: <5.0.2.1.0.20010315120338.0282b750@kalador.com> X-Sender: gutz@kalador.com X-Mailer: QUALCOMM Windows Eudora Version 5.0.2 Date: Thu, 15 Mar 2001 12:03:51 -0800 To: Gerald Gutierrez , pgsql-sql@postgresql.org From: Gerald Gutierrez Subject: Re: VACUUM kills Index Scans ?! In-Reply-To: <5.0.2.1.0.20010315113535.0282b750@kalador.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200103/316 X-Sequence-Number: 1980 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