Hi.
System Configuration
---------------------
Architecture: Intel Pentium
Operating System: FreeBSD 4.2-STABLE
PostgreSQL version: PostgreSQL-7.1.2
Compiler used: gcc 2.95.2
Description of problem
----------------------
Some data-tables accumulate infromation from the system (by INSERT
operations). And then old data has been deleted periodically (by DELETE
operations). Also, I run "VACUUM analyze" command on this tables. But physically
database indexs don't cleans. I mean, that size of index files in
pgsql/data/base/... always grows.
For example, size of table-file was about 2Mb, but size of index - 101Mb.
Only DROP/CREATE index operations can corrects this problem, but ony
temporary. Indexs was created by implicit keyword "UNIQUE", while I created
table (on one column). Or from "psql" console by command:
CREATE UNIQUE INDEX idx_name on table_name(column_name);
Index column types are int4 and numeric(10).
Also I had this problem on server version 7.0.*, but It was not important
thing at that time. Upgrade procedure (from 7.0.* to 7.1.2) I made correctly
without any problems (with backup/restore database).
Vaccuum process from pgsql log ("dirty" indexes):
2001-10-16 00:54:20 NOTICE: Pages 265: Changed 0, reaped 176, Empty 0, New 0; T
up 10350: Vac 10198, Keep/VTL 0/0, Crash 0, UnUsed 291, MinLen 92, MaxLen 104; R
e-using: Free/Avail. Space 1034252/1032760; EndEmpty/Avail. Pages 0/142. CPU 0.0
0s/0.01u sec.
2001-10-16 00:55:26 NOTICE: Index adj30_1_d10_id_key: Pages 13984; Tuples 10350
: Deleted 10198. CPU 1.37s/0.27u sec.
2001-10-16 00:55:47 NOTICE: Index adj30_1_r_id_key: Pages 13658; Tuples 10350:
Deleted 10198. CPU 1.11s/0.31u sec.
2001-10-16 00:55:50 NOTICE: Rel adj30_1: Pages: 265 --> 134; Tuple(s) moved: 56
76. CPU 0.11s/0.59u sec.
2001-10-16 00:56:57 NOTICE: Index adj30_1_d10_id_key: Pages 13989; Tuples 10350
: Deleted 5676. CPU 1.20s/0.18u sec.
2001-10-16 00:57:19 NOTICE: Index adj30_1_r_id_key: Pages 13658; Tuples 10350:
Deleted 5676. CPU 1.20s/0.15u sec.
2001-10-16 00:57:19 NOTICE: Analyzing...
Table "adj30_1"
Attribute | Type | Modifier
-----------+--------------------------+-------------
t1_t | timestamp with time zone |
t2_t | timestamp with time zone |
s0_0 | smallint | default 0
s2_0 | character(1) |
s2_1 | character(1) | default 't'
s0_2 | smallint |
s1_20 | integer |
s1_21 | integer |
s1_1 | integer | default 0
flags0 | integer | default 0
d10_id | numeric(20,0) |
r_id | integer | not null
Indices: adj30_1_d10_id_key,
adj30_1_r_id_key
Constraints: (t2_t NOTNULL)
(t1_t NOTNULL)
(flags0 NOTNULL)
((s1_1 NOTNULL) AND (s1_1 >= 0))
(s1_21 NOTNULL)
(s1_20 NOTNULL)
(s0_2 NOTNULL)
(s2_1 NOTNULL)
(s2_0 NOTNULL)
May be I must use another operations for index control ?
Good luck.
----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
