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.fiche@aqsacom.com
tél : 01 69 29 36 18