pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gerald Gutierrez <gutz@kalador.com>
To: pgsql-sql@postgresql.org
Cc: gutz@colo.kalador.com
Subject: VACUUM kills Index Scans ?!
Date: Thu, 15 Mar 2001 11:42:21 -0800
Message-ID: <5.0.2.1.0.20010315113535.0282b750@kalador.com> (raw)


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




view thread (6+ messages)  latest in thread

Message-ID: <5.0.2.1.0.20010315113535.0282b750@kalador.com>
Permalink:  ../5.0.2.1.0.20010315113535.0282b750@kalador.com/
Also on:    postgresql.org/message-id/5.0.2.1.0.20010315113535.0282b750@kalador.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: gutz@kalador.com, gutz@colo.kalador.com
  Subject: Re: VACUUM kills Index Scans ?!
  In-Reply-To: <5.0.2.1.0.20010315113535.0282b750@kalador.com>
  References: <5.0.2.1.0.20010315113535.0282b750@kalador.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox