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
