Hello everyone,
The app that created this db is written by me for a change. But I've
done simple VACUUM ANALYZE on the biggest table in db and got this:
before VACUUM ANALYZE:
hrs=# SELECT relpages * 8192 AS size_in_bytes, relname FROM pg_class
WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =
'public') ORDER BY size_in_bytes DESC LIMIT 10;
size_in_bytes | relname
---------------+--------------------------------------
30474240 | hosts
548864 | reservation
106496 | reservation_hosts
49152 | reservation_businessneed_idx
40960 | hosts_ip_idx
40960 | hosts_hostname_idx
40960 | hosts_location_idx
40960 | hosts_additional_info_idx
40960 | reservation_status_idx
40960 | reservation_hosts_reservation_id_idx
(10 rows)
After:
hrs=# vacuum analyze hosts;
VACUUM
hrs=# SELECT relpages * 8192 AS size_in_bytes, relname FROM pg_class
WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =
'public') ORDER BY size_in_bytes DESC LIMIT 10;
size_in_bytes | relname
---------------+---------------------------
82206720 | hosts
4194304 | hosts_ip_idx
3842048 | hosts_pkey
3522560 | hosts_hostname_idx
3416064 | hosts_location_idx
3022848 | hosts_additional_info_idx
2482176 | hosts_os_update_idx
2367488 | hosts_cpu_idx
2359296 | hosts_up_n_running_idx
2334720 | hosts_os_kind_id_idx
(10 rows)
W T F ?!
REINDEX helped:
hrs=# reindex table hosts;
REINDEX
hrs=# SELECT relpages * 8192 AS size_in_bytes, relname FROM pg_class
WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =
'public') ORDER BY size_in_bytes DESC LIMIT 10;
size_in_bytes | relname
---------------+--------------------------------------
82206720 | hosts
548864 | reservation
106496 | reservation_hosts
49152 | reservation_businessneed_idx
49152 | hosts_ip_idx
40960 | reservation_status_idx
40960 | reservation_hosts_reservation_id_idx
40960 | reservation_hosts_host_id_idx
40960 | hosts_hostname_idx
40960 | hosts_location_idx