From: | "postgres Emanuel CALVO FRANCO" <postgres(dot)arg(at)gmail(dot)com> |
---|---|
To: | pgsql-es-ayuda(at)postgresql(dot)org |
Subject: | Interesante |
Date: | 2008-09-30 15:41:22 |
Message-ID: | f205bb120809300841h1814c134o950d831cc2e76843@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-es-ayuda |
Siguiendo el mailing list de hackers, vi algo interesante.
Resulta ser que en un caso luego de realizar un VACUUM FULL, los
indices crecieron por lo
que recomiendan luego realizar un REINDEX.
postgres=> select relname, pg_size_pretty( pg_relation_size( oid ) ),
pg_size_pretty( pg_total_relation_size( oid ) ) from pg_class where
relnamespace = ( select oid from pg_namespace where nspname = 'web' )
order by pg_relation_size( oid ) desc;
relname | pg_size_pretty | pg_size_pretty
-----------------------+----------------+----------------
table_1 | 90 MB | 153 MB
url | 67 MB | 101 MB
uk_table-1_url | 63 MB | 63 MB
uk_url_url | 34 MB | 34 MB
link_prefix_pkey | 16 kB | 16 kB
random_url_seq | 8192 bytes | 8192 bytes
link_prefix | 8192 bytes | 32 kB
(7 rows)
postgres=> vacuum full;
WARNING: skipping "pg_type" --- only table or database owner can vacuum it
<a lot of similar warnings>
VACUUM
postgres=> select relname, pg_size_pretty( pg_relation_size( oid ) ),
pg_size_pretty( pg_total_relation_size( oid ) ) from pg_class where
relnamespace = ( select oid from pg_namespace where nspname = 'web' )
order by pg_relation_size( oid ) desc;
relname | pg_size_pretty | pg_size_pretty
-----------------------+----------------+----------------
table_1 | 75 MB | 147 MB
uk_table-1_url | 72 MB | 72 MB
url | 67 MB | 101 MB
uk_url_url | 34 MB | 34 MB
link_prefix_pkey | 16 kB | 16 kB
random_url_seq | 8192 bytes | 8192 bytes
link_prefix | 8192 bytes | 32 kB
(7 rows)
"... Another disadvantage of VACUUM FULL is that while it reduces
table size, it does not reduce index size proportionally; in fact it
can make indexes larger."
and in the next section:
"... Also, moving a row requires transiently making duplicate index
entries for it (the entry pointing to its new location must be made
before the old entry can be removed); so moving a lot of rows this way
causes severe index bloat. "
Quizas a algunos le interese.
From | Date | Subject | |
---|---|---|---|
Next Message | Espartano | 2008-09-30 15:46:36 | Re: postgres para ibm pseries powerpc |
Previous Message | postgres Emanuel CALVO FRANCO | 2008-09-30 15:32:01 | Re: Re: [pgsql-es-ayuda] Encuesta para análisis para el proyecto de creación de una certificación para PostgreSQL |