Postgres Pro
Downloads
Home   >   mailing lists

Thread: DELETE TABLE xx do not erase index file after a vacuum full ?

DELETE TABLE xx do not erase index file after a vacuum full ?

From
Hervé Piedvache
Date:
Bug or problem with PostgreSQL ?

                            version
---------------------------------------------------------------
 PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4

select * from my_table;
   id             |   login    | passwd  | state |      email
--------------+-----------+------------+-------+------------------
(0 rows)

I had before about 800 000 lines inside ...

I have done a :
DELETE from my_table;

Now If I look at the data/bases/ folder I see that I still have a 35 Mb file
for one of the index of this table ... ???

VACUUM FULL VERBOSE ANALYSE; have been done on this table before, but I still
have for this index this message :

INFO:  index "email_my_table_key" now contains 2 row versions in 4313 pages
DETAIL:  0 index row versions were removed.
4309 index pages have been deleted, 4309 are currently reusable.
CPU 0.01s/0.01u sec elapsed 0.02 sec.

What happend ? Why the index file is still here ?

Thanks for your help !
--
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

Re: DELETE TABLE xx do not erase index file after a vacuum full ?

From
Tom Lane
Date:
=?iso-8859-15?q?Herv=E9_Piedvache?= <herve@elma.fr> writes:
> I have done a :
> DELETE from my_table;

TRUNCATE would serve you a lot better.

For major reductions in table size VACUUM FULL is not the best tool;
you really want to reconstruct the indexes from scratch.  Consider
REINDEX, CLUSTER, or (in this case) TRUNCATE.

            regards, tom lane

Re: DELETE TABLE xx do not erase index file after a vacuum full ?

From
Hervé Piedvache
Date:
Tom,

Le jeudi 1 Juillet 2004 19:22, Tom Lane a écrit :
> =?iso-8859-15?q?Herv=E9_Piedvache?= <herve@elma.fr> writes:
> > I have done a :
> > DELETE from my_table;
>
> TRUNCATE would serve you a lot better.

Impossible for this table because it's used by another table with a
referential integrity ... so truncate is refused ...

> For major reductions in table size VACUUM FULL is not the best tool;
> you really want to reconstruct the indexes from scratch.  Consider
> REINDEX, CLUSTER, or (in this case) TRUNCATE.
>

OK Thanks REINDEX is perfect ! ;o)

regards,
--
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902