DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=ah1x3PohhLJQHVxVXriubJvmOi1uYb5fWefN6mq7T3XMbXxucmy2/zzuqC5i6Miksfg8+0AQB1ritTsopC/kUPxmwr1v7rmTq1swm5gV2xCMeXMY6Hv7U+Q96wBpUS4x1Zpo9vUEHf+hHTKsp7Phdi9KXe10afL9rlARWDxrPCc=
	;
Message-ID: <20050616180442.8983.qmail@web33703.mail.mud.yahoo.com>
Date: Thu, 16 Jun 2005 11:04:41 -0700 (PDT)
From: ken shaw <kshaw987@yahoo.com>
Subject: How to determine whether to VACUUM or CLUSTER
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1679030748-1118945081=:7587"
Content-Transfer-Encoding: 8bit

--0-1679030748-1118945081=:7587
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit


Hi All,

I have an app that updates a PostgreSQL db in a batch fashion. After each batch (or several batches), it issues VACUUM and ANALYZE calls on the updated tables. Now I want to cluster some tables for better performance. I understand that doing a VACUUM and a CLUSTER on a table is wasteful as the CLUSTER makes the VACUUM superfluous. The app does not have a built-in list of the tables and whether each is clustered or not. It looks to me as if the only way to determine whether to issue a VACUUM (on a non-clustered table) or a CLUSTER (on a clustered table) is to query the table "pg_index", much like view "pg_indexes" does, for the column "indisclustered". Is this right?

Also, how expensive is CLUSTER compared to VACUUM? Does CLUSTER read in the whole table, sort it, and write it back out? Or write out a completely new file? Is the time for a CLUSTER the same whether one row is out of place or the table is completely disordered?

Thanks,

Ken


---------------------------------
Discover Yahoo!
 Find restaurants, movies, travel & more fun for the weekend. Check it out!
--0-1679030748-1118945081=:7587
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

<DIV><FONT size=2>
<P>Hi All,</P>
<P>I have an app that updates a PostgreSQL db in a batch fashion. After each batch (or several batches), it issues VACUUM and ANALYZE calls on the updated tables. Now I want to cluster some tables for better performance. I understand that doing a VACUUM and a CLUSTER on a table is wasteful as the CLUSTER makes the VACUUM superfluous. The app does not have a built-in list of the tables and whether each is clustered or not. It looks to me as if the only way to determine whether to issue a VACUUM (on a non-clustered table) or a CLUSTER (on a clustered table) is to query the table "pg_index", much like view "pg_indexes" does, for the column "indisclustered". Is this right?</P>
<P>Also, how expensive is CLUSTER compared to VACUUM? Does CLUSTER read in the whole table, sort it, and write it back out? Or write out a completely new file? Is the time for a CLUSTER the same whether one row is out of place or the table is completely disordered?</P>
<P>Thanks,</P>
<P>Ken</P></FONT></DIV><p>
		<hr size=1>Discover Yahoo!<br> 
Find restaurants, movies, travel & more fun for the weekend. <a href="http://us.rd.yahoo.com/evt=32658/*http://discover.yahoo.com/weekend.html">Check it out!</a>
--0-1679030748-1118945081=:7587--