Skip site navigation (1) Skip section navigation (2)

Interesante

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 (view raw or flat)
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.

Responses

pgsql-es-ayuda by date

Next:From: EspartanoDate: 2008-09-30 15:46:36
Subject: Re: postgres para ibm pseries powerpc
Previous:From: postgres Emanuel CALVO FRANCODate: 2008-09-30 15:32:01
Subject: Re: Re: [pgsql-es-ayuda] Encuesta para análisis para el proyecto de creación de una certificación para PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group