Обсуждение: 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:

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