Use of VACUUM / REINDEX

From: "Patrick Fiche" <patrick(dot)fiche(at)aqsacom(dot)com>
To: "PostgreSQL - General (E-mail)" <pgsql-general(at)postgresql(dot)org>
Subject: Use of VACUUM / REINDEX
Date: 2002-10-16 09:09:28
Message-ID: 85058ADF852DD5118FD50002A528A5B6079A88@SERVEUR
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I think I don't understand how VACCUM is working...
I have an empty table ( many inserts and deletes were done but now it's
empty ).

This table is called D_FLUX and has 3 indexes : pk_d_flux, sk1_d_flux,
sk2_d_flux

When I look in the pg_class table here is what I get

relname relpages reltuples
pk_d_flux 5892 0
sk1_d_flux 5883 0
sk3_d_flux 4418 0
d_flux 0 0

As you can see, indexes are using space even with no data.

So I run
VACUUM FULL VERBOSE D_FLUX

NOTICE: --Relation d_flux--
NOTICE: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/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 elapsed 0.00 sec.
NOTICE: Index pk_d_flux: Pages 5892; Tuples 0.
CPU 0.18s/0.03u sec elapsed 7.81 sec.
NOTICE: Index sk1_d_flux: Pages 5883; Tuples 0.
CPU 0.19s/0.01u sec elapsed 11.95 sec.
NOTICE: Index sk3_d_flux: Pages 4418; Tuples 0.
CPU 0.17s/0.03u sec elapsed 9.86 sec.

In pg_class table, there is no modification and the files ( indicated by
relfilenodes ) are some Mb large...

The only way to have my indexes emptied is to use REINDEX D_FLUX

Is it the correct way of working ?

And if it's the case, I create and drop many temporary tables and it's just
like the indexes pg_attribute_relid_attnam_index and
pg_attribute_relid_attnum_index are growing too...
The REINDEX of these indexes need FORCE flag because they are system indexes
and to do so, I have to launch postgres with -O -P options....
So I have to stop the exploitation regularly and it's not acceptable...
Is there any other solution ?

My version is : 7.2.1

Patrick Fiche
email : patrick(dot)fiche(at)aqsacom(dot)com
tél : 01 69 29 36 18

Browse pgsql-general by date

  From Date Subject
Next Message Neil Fraser 2002-10-16 09:21:30 Corrupt database
Previous Message Shridhar Daithankar 2002-10-16 06:39:34 Re: [HACKERS] [GENERAL] Postgres-based system to run .org registry?