Message-ID: <4293EA52.20005@arbash-meinel.com>
Date: Tue, 24 May 2005 22:00:34 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: SpaceBallOne <space_ball_one@hotmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Can anyone explain this: duplicate dbs.
References: <BAY14-DAV38E580C92EFF811F1266ACC0E0@phx.gbl>
	<4293E553.6070400@arbash-meinel.com>
	<BAY14-DAV16F2B796085B6591A93AFACC0E0@phx.gbl>
In-Reply-To: <BAY14-DAV16F2B796085B6591A93AFACC0E0@phx.gbl>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig44F58C7791E1887BD94AC986"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig44F58C7791E1887BD94AC986
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

SpaceBallOne wrote:

>> What version of postgres?
>
>
> 8.0.2 ... but I think I've seen this before on 7.3 ...
>
>> There are a few possibilities. If you are having a lot of updates to the
>> table, you can get index bloat. And vacuum doesn't fix indexes. You have
>> to "REINDEX" to do that. Though REINDEX has the same lock that VACUUM
>> FULL has, so you need to be a little careful with it.
>
>
>> Probably better is to do CLUSTER, as it does a REINDEX and a sort, so
>> your table ends up nicer when you are done.
>
>
> Thanks, will try those next time this problem crops up (i just deleted
> / recreated the database to speed things for its users in the office
> ... probably should have held off to see if I could find a solution
> first!).
>
> Yes, the database / table-in-question does have a lot of updates,
> deletes, and new rows (relatively speaking for a small business).
>
> Would CLUSTER / REINDEX still have an effect if our queries were done
> via sequential scan? This is a old database (as in built by me when i
> was just starting to learn unix / postgres) so the database design is
> pretty horrible (little normalisation, no indexes).

Well, my first recommendation is to put in some indexes. :) They are
relatively easy to setup and can drastically improve select performance.

What version of postgres are you using?
What does it say at the end of "VACUUM FULL ANALYZE VERBOSE", that
should tell you how many free pages were reclaimed and how big your free
space map should be.

If you only did 1 VACUUM FULL, you might try another, as it sounds like
your tables aren't properly filled. I'm pretty sure vacuum only removes
empty pages/marks locations for the free space map so they can be
re-used, while vacuum full will move entries around to create free pages.

It sounds like it didn't do it properly.

But even so, CLUSTER is still your friend, as it allows you to "presort"
the rows in your tables.

>
> Have taken Chris's advice onboard too and setup cron to do a vacuumdb
> hourly instead of my weekly vacuum.
>
> Cheers,
>
> Dave.
>
>
John
=:->


--------------enig44F58C7791E1887BD94AC986
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Cygwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCk+pSJdeBCYSNAAMRAq1LAJ9wtuOuGBxaVGPZ47cie/X0l/N+6QCggXA9
xZ911qobTXG6JwsejW531u8=
=nJGz
-----END PGP SIGNATURE-----

--------------enig44F58C7791E1887BD94AC986--