Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA78834 for ; Mon, 25 Oct 1999 10:46:41 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA17669; Mon, 25 Oct 1999 10:45:30 -0400 (EDT) To: Postgres cc: "pgsql-sql@postgresql.org" Subject: Re: [SQL] Vacuum takes more than 1 hr In-reply-to: Your message of Mon, 25 Oct 1999 03:52:18 -0700 <001001bf1ed7$01dc9b20$9befaace@usedcars.com> Date: Mon, 25 Oct 1999 10:45:30 -0400 Message-ID: <17667.940862730@sss.pgh.pa.us> From: Tom Lane Postgres writes: > I have a table with about 30 columns and 200K rows. When I perform a vacuum > on it (no analyze) it takes usually a little more than 1 hour. Hmm, doesn't seem all that large. One question is whether you are all the way up-to-date on Postgres --- some performance problems in vacuum were fixed recently. (The problems were excessive memory usage, actually, but that could translate to long runtime if the process started to swap. Does the backend that's doing the vacuum seem to grow to a size much larger than it starts at?) I don't recall whether this patch is in 6.5.2 or not, but it will be in 6.5.3, or you could pull the current REL6_5 branch sources from the CVS server. A performance problem that still remains is that vacuum seems unreasonably slow at updating indexes. Some people have found that dropping and recreating indexes around a vacuum nets out faster than letting vacuum do it. You should also ask yourself whether each index on the table is earning its keep --- each one costs time on every insert or update, quite aside from vacuum. Only the indexes that actually get used for your common queries are likely to be worth their overhead. regards, tom lane