From: | Yudhvir Singh Sidhu <ysidhu(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | How to Find Cause of Long Vacuum Times - NOOB Question |
Date: | 2007-05-05 22:57:25 |
Message-ID: | 463D0BD5.3010404@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Steinar H. Gunderson | 2007-05-05 23:40:14 | Re: How to Find Cause of Long Vacuum Times - NOOB Question |
Previous Message | Sebastian Hennebrueder | 2007-05-05 15:54:33 | Re: Feature Request --- was: PostgreSQL Performance Tuning |