Обсуждение: CLUSTER cannot complete
Hello everyone, Resolved, I've done: hrs=# CLUSTER hosts_ip_idx ON hosts; CLUSTER hrs=# hrs=# 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 ---------------+-------------------------------------- 548864 | reservation 376832 | hosts 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 (10 rows) Why such a dramatic reduction in table size -- from 80MB to 0.55MB?! I didn't add or delete many rows from that table. Quite a lot of UPDATEs are running daily, though. Regards, mk
On Mon, Feb 15, 2010 at 9:54 AM, Marcin Krol <mrkafk@gmail.com> wrote: > I didn't add or delete many rows from that table. Quite a lot of UPDATEs are > running daily, though. Every update is equal to insert + delete, except in certain circumstances in 8.3 and above.
Vick Khera wrote:
Well, it's still an insert + delete of the actual data row itself even in 8.3, the later versions just optimize how that impacts the index related changes there better.
On Mon, Feb 15, 2010 at 9:54 AM, Marcin Krol <mrkafk@gmail.com> wrote:I didn't add or delete many rows from that table. Quite a lot of UPDATEs are running daily, though.Every update is equal to insert + delete, except in certain circumstances in 8.3 and above.
Well, it's still an insert + delete of the actual data row itself even in 8.3, the later versions just optimize how that impacts the index related changes there better.
-- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us