system indices growing huge

I'm observing a strange effect on a PostgreSQL installation. The system
indices of one of the databases are growing large without obvious reason.

You'll find a summary of version numbers at the end of this mail.

Two databases are in active use: immoma is the production database, while
testdb is for testing only. Every 30 minutes a cron job feeds the production
data into the test database. A dump of the immoma database is created and
the testdb database recreated using the dump. Therefore, the contents of
the testdb database are mostly identical to the contents of the immoma
database. I've been working for some time using both databases and
functionally the approch is ok. But the system indices of the testdb
database
are now several times larger than those of the immoma database, while the
database contents are identical. Moreover, psql-commands like \d takes a
much longer time to execute against testdb than against immoma.

The script used for the update is as follows. The .cstr files do only
contain
username and password, each followed by a linefeed. The lines of the
commands
have been wrapped in order to better fit your screen.

/usr/local/pgsql/bin/pg_dump -R -c -U immoma immoma
> /usr/local/httpd/htdocs/kunden/web41/datenbank/export.sql
< /usr/local/httpd/htdocs/kunden/web41/datenbank/pg_dump.cstr

/usr/local/pgsql/bin/psql -f
/usr/local/httpd/htdocs/kunden/web41/datenbank/export.sql
testdb immoma < /usr/local/httpd/htdocs/kunden/web41/datenbank/psql.cstr

/usr/local/pgsql/bin/psql -f
/usr/local/httpd/htdocs/kunden/web41/datenbank/sitzungen.sql
testdb immoma < /usr/local/httpd/htdocs/kunden/web41/datenbank/psql.cstr

Each of the two databases contains about 2500 rows in total, distributed
among
some 20 tables. The largest table does not exceed 1300 rows.

The size of testdb is continuously increasing, although it is recreated at
regular
intervals using the data dumped from the immoma database, which increases
at most by 100 rows per day.

Here is a summary of the sizes of the datafiles on disk. A totel of 941 MB
is definitely
unrealistic for the numbers mentioned above.

web41(at)julius64:~/datenbank > du -h db_cluster
1.7M db_cluster/base/1
1.7M db_cluster/base/16555
4.0k db_cluster/base/16559/pgsql_tmp
81M db_cluster/base/16559
4.0k db_cluster/base/16560/pgsql_tmp
710M db_cluster/base/16560
795M db_cluster/base
120k db_cluster/global
129M db_cluster/pg_xlog
18M db_cluster/pg_clog
941M db_cluster

Below I list the page count summaries and single page counts for the 40 most
significant
relations in each of the databases, including system tables. As you see, the
increase in
size is not caused by data entered by the user, but due to overhead in
system indices.

immoma=# select sum (relpages) from pg_class;
sum
-------
10316
(1 row)

immoma=# select relname, relfilenode, relpages, relowner from pg_class order
by relpages desc limit 40;
relname | relfilenode | relpages | relowner
---------------------------------+-------------+----------+----------
pg_attribute_relid_attnam_index | 16426 | 3613 | 1
pg_class_relname_index | 16429 | 1699 | 1
pg_attribute_relid_attnum_index | 16427 | 1504 | 1
objekte | 8283011 | 1443 | 100
pg_class_oid_index | 16428 | 615 | 1
pg_type_typname_index | 16458 | 225 | 1
pg_index_indrelid_index | 16435 | 105 | 1
pg_index_indexrelid_index | 16436 | 105 | 1
pg_type_oid_index | 16457 | 81 | 1
objekte_kriterien_idx | 25785967 | 56 | 100
sitzungen | 8282989 | 47 | 100
sitzungen_pkey | 8282991 | 37 | 100
objekte_pkey | 8283016 | 35 | 100
pg_proc | 1255 | 31 | 1
pg_attrdef_adrelid_adnum_index | 16425 | 29 | 1
pg_proc_proname_narg_type_index | 16446 | 29 | 1
medien | 8282979 | 23 | 100
pg_attribute | 1249 | 21 | 1
sitzungen_zeitpunkt_idx | 8286797 | 21 | 100
pg_statistic | 16408 | 21 | 1
anwender | 8282943 | 20 | 100
objekte_n_eigene_idx | 25785957 | 15 | 100
objekte_kaeuflichb_idx | 25785955 | 14 | 100
objekte_n_etagen_idx | 25785963 | 14 | 100
objekte_markt_id_idx | 25785949 | 13 | 100
objekte_anwender_id_idx | 25785950 | 13 | 100
objekte_standort_id_idx | 25785951 | 13 | 100
objekte_aktivb_idx | 25785954 | 13 | 100
objekte_preis_cent_idx | 25785958 | 13 | 100
objekte_nk_cent_idx | 25785959 | 13 | 100
objekte_baujahr_idx | 25785966 | 13 | 100
pg_description | 16416 | 12 | 1
pg_toast_8283002 | 8283004 | 12 | 100
objekte_typ_id_idx | 25785952 | 12 | 100
objekte_n_global_idx | 25785956 | 12 | 100
objekte_grundstueck_idx | 25785960 | 12 | 100
objekte_nutzflaeche_idx | 25785962 | 12 | 100
objekte_n_zimmer_idx | 25785964 | 12 | 100
objekte_zeitpunkt_idx | 25785953 | 11 | 100
objekte_wohnflaeche_idx | 25785961 | 11 | 100
(40 rows)

testdb=# select sum (relpages) from pg_class;
sum
-------
50605
(1 row)

testdb=# select relname, relfilenode, relpages, relowner from pg_class order
by relpages desc limit 40;
relname | relfilenode | relpages | relowner
---------------------------------+-------------+----------+----------
pg_attribute_relid_attnam_index | 16426 | 25196 | 1
pg_attribute_relid_attnum_index | 16427 | 10470 | 1
pg_class_relname_index | 16429 | 7400 | 1
pg_class_oid_index | 16428 | 3480 | 1
pg_type_typname_index | 16458 | 947 | 1
pg_type_oid_index | 16457 | 519 | 1
pg_index_indrelid_index | 16435 | 505 | 1
pg_index_indexrelid_index | 16436 | 505 | 1
pg_attribute | 1249 | 403 | 1
pg_attrdef_adrelid_adnum_index | 16425 | 178 | 1
pg_attrdef | 16384 | 124 | 1
objekte | 27150520 | 99 | 100
pg_index | 16390 | 40 | 1
pg_type | 1247 | 32 | 1
pg_proc | 1255 | 31 | 1
pg_proc_proname_narg_type_index | 16446 | 29 | 1
medien | 27150488 | 16 | 100
pg_description | 16416 | 12 | 1
objekte_kriterien_idx | 27155393 | 11 | 100
pg_operator | 16392 | 10 | 1
pg_toast_25983663 | 25983665 | 10 | 100
artikel_tmp | 25983663 | 10 | 100
pg_toast_27150432 | 27150434 | 10 | 100
branchen | 27150432 | 10 | 100
anbieter | 27150440 | 10 | 100
pg_toast_27150444 | 27150446 | 10 | 100
pg_toast_27150452 | 27150454 | 10 | 100
pg_toast_27150458 | 27150460 | 10 | 100
pg_toast_27150465 | 27150467 | 10 | 100
pg_toast_27150471 | 27150473 | 10 | 100
pg_toast_27150478 | 27150480 | 10 | 100
pg_toast_27150488 | 27150490 | 10 | 100
pg_toast_27150493 | 27150495 | 10 | 100
bildvorschau | 27150493 | 10 | 100
pg_toast_27150503 | 27150505 | 10 | 100
pg_toast_27150511 | 27150513 | 10 | 100
test | 27150517 | 10 | 100
pg_toast_27150520 | 27150522 | 10 | 100
plz | 27150526 | 10 | 100
pg_toast_27150530 | 27150532 | 10 | 100
(40 rows)

What is the reason for this increase in size? How can I prevent it?

I'm stuck with this. Any help is welcome.

Regards, Daniel Alvarez <d-alvarez(at)gmx(dot)de>

web41(at)julius64:~ > uname -a
Linux julius64 2.4.14 #34 Wed Jan 23 17:41:57 MET 2002 i686 unknown

web41(at)julius64:~ > psql -V
psql (PostgreSQL) 7.2
Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
Portions Copyright (c) 1996, Regents of the University of California
Read the file COPYRIGHT or use the command \copyright to see the

--
+++ GMX - Mail, Messaging & more http://www.gmx.net +++
NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen!