From: Yudhvir Singh Sidhu <ysidhu@gmail.com>
To: pgsql-performance@postgresql.org
Subject: How to Find Cause of Long Vacuum Times - NOOB Question
Date: Sat, 05 May 2007 15:57:25 -0700
Message-ID: <463D0BD5.3010404@gmail.com> (raw)
I hope someone can help me with this vacuum problem. I can post more
info if needed.
Versions: Postgresql version 8.09 on FreeBSD 6.1
Situation: huge amounts of adds and deletes daily. Running daily vacuums
Problem: Vacuum times jump up from 45 minutes, or 1:30 minutes to 6+
hours overnight, once every 1 to 3 months.
Solutions tried: db truncate - brings vacuum times down. Reindexing
brings vacuum times down.
I know my indexes are getting fragmented and my tables are getting
fragmented. I also know that some of my btree indexes are not being used
in queries. I also know that using "UNIQUE" in a query makes PG ignore
any index.
I am looking for the cause of this. Recently I have been looking at
EXPLAIN and ANALYZE.
1. Running EXPLAIN on a query tells me how my query SHOULD run and
running ANALYZE tells me how it DOES run. Is that correct?
2. If (1) is true, then a difference between the two means my query
plan is messed up and running ANALYZE on a table-level will somehow
rebuild the plan. Is that correct?
3. If (2) is correct, then running ANALYZE on a nightly basis before
running vacuum will keep vacuum times down. Is that correct?
Yudhvir Singh
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ysidhu@gmail.com
Subject: Re: How to Find Cause of Long Vacuum Times - NOOB Question
In-Reply-To: <463D0BD5.3010404@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox