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 по дате отправления: