Lost indexes - Mailing list pgsql-general

Greetings,

  I've been fighting against a very strange behaviour found in PostgreSQL
7.1.2 on a RedHat 6.2. I have a very simple table called site_site and I
lost it's indexes everytime I run a vaccum. Do you know why this happens? Is
there a way to get around or fix this kind of problem?I put a full sequence
of steps I've used to make it happen.

CREATE TABLE site_site (
       id_site              int4 NOT NULL,
       nome                 varchar(30) NOT NULL,
       CONSTRAINT XPKsite_site PRIMARY KEY (id_site)

);

CREATE UNIQUE INDEX XAK1site_site ON site_site
(
       nome
);


[root@dgtao /home]# psql -U postgres gravador
gravador=# \d site_site
              Table "site_site"
 Attribute |         Type          | Modifier
-----------+-----------------------+----------
 id_site   | integer               | not null
 nome      | character varying(30) | not null
Indices: xak1site_site,
         xpksite_site


gravador=# select * from site_site;
 id_site | nome
---------+------
(0 rows)

gravador=# explain select * from site_site where id_site = 1;
NOTICE:  QUERY PLAN:
Index Scan using xpksite_site on site_site  (cost=0.00..8.14 rows=10
width=16)
EXPLAIN

gravador=# explain select * from site_site where nome = 'Vilson';
NOTICE:  QUERY PLAN:
Index Scan using xak1site_site on site_site  (cost=0.00..8.14 rows=10
width=16)
EXPLAIN

gravador=# VACUUM VERBOSE ANALYZE site_site;
NOTICE:  --Relation site_site--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail.
Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index xpksite_site: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
NOTICE:  Index xak1site_site: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
NOTICE:  Analyzing...
VACUUM

gravador=# explain select * from site_site where id_site = 1;
NOTICE:  QUERY PLAN:
Seq Scan on site_site  (cost=0.00..0.00 rows=1 width=16)
EXPLAIN


gravador=# explain select * from site_site where nome = 'Vilson';
NOTICE:  QUERY PLAN:
Seq Scan on site_site  (cost=0.00..0.00 rows=1 width=16)
EXPLAIN

Here we go again. At this time there are data inside the table...

gravador=# insert into site_site values (1, 'Vilson');
INSERT 22798 1

gravador=# explain select * from site_site where id_site = 1;
NOTICE:  QUERY PLAN:
Index Scan using xpksite_site on site_site  (cost=0.00..8.14 rows=10
width=16)
EXPLAIN

gravador=# explain select * from site_site where nome = 'Vilson';
NOTICE:  QUERY PLAN:
Index Scan using xak1site_site on site_site  (cost=0.00..8.14 rows=10
width=16)
EXPLAIN

gravador=# VACUUM VERBOSE ANALYZE site_site;
NOTICE:  --Relation site_site--
NOTICE:  Pages 1: Changed 1, reaped 0, Empty 0, New 0; Tup 1: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 46, MaxLen 46; Re-using: Free/Avail.
Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index xpksite_site: Pages 2; Tuples 1. CPU 0.00s/0.01u sec.
NOTICE:  Index xak1site_site: Pages 2; Tuples 1. CPU 0.00s/0.00u sec.
NOTICE:  Analyzing...
VACUUM

gravador=# explain select * from site_site where id_site = 1;
NOTICE:  QUERY PLAN:
Seq Scan on site_site  (cost=0.00..1.01 rows=1 width=16)
EXPLAIN

gravador=# explain select * from site_site where nome = 'Vilson';
NOTICE:  QUERY PLAN:
Seq Scan on site_site  (cost=0.00..1.01 rows=1 width=16)
EXPLAIN


Thanks in advance!

----------------------------------------------------------------------------
----
José Vilson de Mello de Farias
Software Engineer

Dígitro Tecnologia Ltda - www.digitro.com.br
APC - Customer Oriented Applications
E-mail: vilson.farias@digitro.com.br
Tel.: +55 48 281 7158
ICQ 11866179