Unreasonable size of table pg 8.2.5

Поиск
Список
Период
Сортировка
От Henrik
Тема Unreasonable size of table pg 8.2.5
Дата
Msg-id 5E3240DA-EDE1-48DD-94C2-1921F3F10889@mac.se
обсуждение исходный текст
Ответы Re: Unreasonable size of table pg 8.2.5
Список pgsql-general
Hello list,

I have a table with 135 rows and it still takes up about 360MB with
only small columns. Details below.

db=# vacuum full tbl_archive;
VACUUM
db=# select * from
pg_size_pretty(pg_total_relation_size('tbl_archive'));
  pg_size_pretty
----------------
  360 MB
(1 row)

db=# select * from pg_size_pretty(pg_relation_size('tbl_archive'));
  pg_size_pretty
----------------
  16 kB
(1 row)


Looks like we have a very bloated index.
After reindex
db=# select * from
pg_size_pretty(pg_total_relation_size('tbl_archive'));
  pg_size_pretty
----------------
80 kB
(1 row)

I thought that reindex should not be necessary in 8.2.5? This is not a
big tabel but what I can see is that we have many small updates.
Cheers,
Henke

db=# \d tbl_Archive;
                                                 Table
"public.tbl_archive"
            Column            |            Type
|                          Modifiers
-----------------------------+-----------------------------
+--------------------------------------------------------------
  pk_archive_id               | bigint                      | not null
default nextval(('archive_seq_id'::text)::regclass)
  archive_name                | character varying(255)      |
  archive_backup_type         | character(1)                |
  archive_size                | bigint                      | not null
default 0
  fk_share_id                 | bigint                      |
  archive_complete            | boolean                     | not null
default false
  fk_job_id                   | bigint                      |
  archive_date                | timestamp without time zone | not null
default now()
  archive_nmb_files           | integer                     | not null
default 0
  archive_nmb_folders         | integer                     | not null
default 0
  archive_nmb_file_exceptions | integer                     | not null
default 0
Indexes:
     "tbl_archive_pkey" PRIMARY KEY, btree (pk_archive_id)
     "tbl_archive_idx" btree (archive_complete)
     "tbl_archive_idx1" btree (fk_share_id)
     "tbl_archive_idx2" btree (fk_job_id)
Check constraints:
     "tbl_archive_chk" CHECK (archive_backup_type = 'F'::bpchar OR
archive_backup_type = 'I'::bpchar)
Foreign-key constraints:
     "tbl_archive_fk" FOREIGN KEY (fk_share_id) REFERENCES
tbl_share(pk_share_id) ON UPDATE CASCADE ON DELETE SET NULL
     "tbl_archive_fk1" FOREIGN KEY (fk_job_id) REFERENCES
tbl_job(pk_job_id) ON DELETE SET NULL


В списке pgsql-general по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: autovacuum is stopping automatically....
Следующее
От: Bill Moran
Дата:
Сообщение: Re: Unreasonable size of table pg 8.2.5