On Tue, Oct 20, 2009 at 9:07 AM, Eric Comeau <Eric.Comeau@signiant.com> wrote:
>
> Thanks for this query, I ran it on one of our QA servers and the results
> were interesting when I compare the table size to the primary-key size..
>
> relname | size_alone |
> total_size_incl_indexes
> ----------------------------------------+------------+-------------------------
> job_run_stat_interval | 329 MB | 603 MB
> job_run_stat_interval_idx | 274 MB | 274 MB
> job_run_stat_pkey | 155 MB | 155 MB
> job_run_stat | 67 MB | 222 MB
>
>
If you're puzzled why the primary key index job_run_stat_pkey is using
more space than the table itself without indexes, read here first:
http://www.postgresql.org/docs/current/static/routine-reindex.html
I was able to reproduce your symptom of an index taking up more space
than the table alone in PG 8.3.7 by creating and populating a dummy
table like so. Insert calls to the pg_size_* query in between to watch
what happens to the table and index sizes.
-- create dummy table with just a single column, in an attempt to
reproduce symptom
CREATE TABLE nums_table (num serial PRIMARY KEY);
INSERT INTO nums_table (num) SELECT newnum FROM generate_series(100,
100000) as newnum;
-- cause some table bloat:
UPDATE nums_table SET num = num * -1;
UPDATE nums_table SET num = num * -1;
UPDATE nums_table SET num = num * -1;
-- now: table alone = 14 MB, nums_table_pkey alone also = 14 MB
-- run a VACUUM FULL ANALYZE to get rid of table bloat, but not index bloat
VACUUM FULL ANALYZE nums_table;
-- finally, bring index size down to normal:
REINDEX INDEX "nums_table_pkey";
If you get the same results I got, you should notice that after the
VACUUM FULL ANALYZE, nums_table without indexes takes 3.5 MB, while
the nums_table_pkey by itself takes 14 MB. After issuing REINDEX, the
pkey goes down to 2.2 MB, and the table without indexes goes down to
3.5 MB. YMMV -- different runs produced slightly different numbers for
me, but the overall idea is the same.
Josh
