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

Re: Less rows -> better performance?

From: Andreas Hartmann <andreas(at)apache(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Less rows -> better performance?
Date: 2008-07-21 14:45:10
Message-ID: 4884A0F6.2000501@apache.org (view raw or flat)
Thread:
Lists: pgsql-performance
Guillaume Smet schrieb:
> On Mon, Jul 21, 2008 at 1:25 PM, Andreas Hartmann <andreas(at)apache(dot)org> wrote:
>> SELECT pg_database.datname,
>> pg_size_pretty(pg_database_size(pg_database.datname)) AS size
>> FROM pg_database where pg_database.datname = 'vvz_live_1';
>>
>>    datname    |  size
>> ---------------+---------
>>  vvz_live_1    | 2565 MB
>>
>> I wonder why the actual size is so much bigger than the data-only dump - is
>> this because of index data etc.?
> 
> More probably because the database is totally bloated. Do you run
> VACUUM regularly or did you set up autovacuum?

Thanks for the hint!

I just verified that the autovacuum property is enabled. I did the 
following to prepare the tests:

- setup two test databases, let's call them db_all and db_current
- import the dump from the live DB into both test DBs
- delete the old semester data from db_current, leaving only the current 
data

Both test DBs were 600 MB large after this. I did a VACUUM FULL ANALYZE 
on both of them now. db_all didn't shrink significantly (only 1 MB), 
db_current shrunk to 440 MB. We're using quite a lot of indexes, I guess 
that's why that much data are allocated.

-- Andreas

-- 
Andreas Hartmann, CTO
BeCompany GmbH
http://www.becompany.ch
Tel.: +41 (0) 43 818 57 01

In response to

Responses

pgsql-performance by date

Next:From: Harald Armin MassaDate: 2008-07-21 14:56:27
Subject: Re: Less rows -> better performance?
Previous:From: Christian GRANDINDate: 2008-07-21 14:00:23
Subject: Re: Less rows -> better performance?

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