X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 089325292A
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 21 Jun 2005 12:19:57 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 31059-06
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 21 Jun 2005 15:19:51 +0000 (GMT)
Received: from sccmmhc91.asp.att.net (sccmmhc91.asp.att.net [204.127.203.211])
	by svr1.postgresql.org (Postfix) with ESMTP id 02C8D528D0
	for <pgsql-performance@postgresql.org>;
	Tue, 21 Jun 2005 12:19:50 -0300 (ADT)
Received: from juju.arbash-meinel.com ([12.214.18.81])
	by sccmmhc91.asp.att.net (sccmmhc91) with ESMTP
	id <20050621151950m9100ngocve>; Tue, 21 Jun 2005 15:19:50 +0000
Received: from [192.168.1.11] (mail [192.168.1.1])
	by juju.arbash-meinel.com (Postfix) with ESMTP id 5B24555FB6;
	Tue, 21 Jun 2005 10:19:49 -0500 (CDT)
Message-ID: <42B83015.7060708@arbash-meinel.com>
Date: Tue, 21 Jun 2005 10:19:49 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Jone C <jonecster@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow growing table
References: <6556952605060609485c622403@mail.gmail.com>	
	<20050606170037.GA1177@uio.no> <20050606171253.GA1655@uio.no>
	<65569526050621080553350f62@mail.gmail.com>
In-Reply-To: <65569526050621080553350f62@mail.gmail.com>
X-Enigmail-Version: 0.92.0.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig633606F0933A7E320962B1E4"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.035 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200506/391
X-Sequence-Number: 13028

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

Jone C wrote:

>>On second thought... Does a VACUUM FULL help? If so, you might want to
>>increase your FSM settings.
>>
>>
>
>Thank you for the reply, sorry for delay I was on holiday.
>
>I tried that it had no effect. I benchmarked 2x before, peformed
>VACUUM FULL on the table in question post inserts, then benchmarked 2x
>after. Same results...
>
>Should I try your suggestion on deleting the indexes? This table needs
>to be accessible for reads at all times however though...
>
>thank you kindly
>
>

I believe dropping an index inside a transaction is only visible to that
transaction. (Can someone back me up on this?)
Which means if you did:

BEGIN;
DROP INDEX <index in question>;
CREATE INDEX <same index> ON <same stuff>;
COMMIT;

The only problem is that if you are using a unique or primary key index,
a foreign key which is referencing that index would have to be dropped
and re-created as well. So you could have a pretty major cascade effect.

A better thing to do if your table only has one (or at least only a few)
indexes, would be to CLUSTER, which is effectively a VACUUM FULL + a
REINDEX (plus sorting the rows so that they are in index order). It
holds a full lock on the table, and takes a while, but when you are
done, things are cleaned up quite a bit.

You might also try just a REINDEX on the indexes in question, but this
also holds a full lock on the table. (My DROP + CREATE might also as
well, I'm not really sure, I just think of it as a way to recreate
without losing it for other transactions)

John
=:->


--------------enig633606F0933A7E320962B1E4
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

iD8DBQFCuDAVJdeBCYSNAAMRAlzCAKCbu2YaappfY1/Nfuq9h+wCDyE9dwCfb9FI
B0ZPEUSyZPHLibLTSoUodTE=
=Vtuf
-----END PGP SIGNATURE-----

--------------enig633606F0933A7E320962B1E4--