*What I did was, I issued the following command * * * *$vacuumdb -d prodDB -t orders -f -z -v * * * * * * "Orders": found 0 removable, 27164544 nonremovable row versions in 518971 pages* *DETAIL: 27126176 dead row versions cannot be removed yet.* *Nonremovable row versions range from 118 to 213 bytes long.* *There were 10425 unused item pointers.* *Total free space (including removable row versions) is 35613716 bytes.* *0 pages are or will become empty, including 0 at the end of the table.* *89274 pages containing 12011420 free bytes are potential move destinations. * *CPU 15.53s/16.55u sec elapsed 62.78 sec.* *INFO: index "idx_orders_id" now contains 27164544 row versions in 95569 pages* *DETAIL: 0 index row versions were removed.* *0 index pages have been deleted, 0 are currently reusable.* *CPU 3.18s/4.35u sec elapsed 20.52 sec.* *INFO: "Orders": moved 6 row versions, truncated 518971 to 518971 pages* *DETAIL: CPU 0.08s/0.08u sec elapsed 7.69 sec.* *INFO: index " idx_orders_id" now contains 27164544 row versions in 95569 pages* *DETAIL: 6 index row versions were removed.* *0 index pages have been deleted, 0 are currently reusable.* *CPU 2.25s/2.78u sec elapsed 14.97 sec.* *INFO: vacuuming "pg_toast.pg_toast_1059337"* *INFO: "pg_toast_1059337": found 0 removable, 0 nonremovable row versions in 0 pages* *DETAIL: 0 dead row versions cannot be removed yet.* *Nonremovable row versions range from 0 to 0 bytes long.* *There were 0 unused item pointers.* *Total free space (including removable row versions) is 0 bytes.* *0 pages are or will become empty, including 0 at the end of the table.* *0 pages containing 0 free bytes are potential move destinations.* *CPU 0.00s/0.00u sec elapsed 0.00 sec.* *INFO: index "pg_toast_1059337_index" now contains 0 row versions in 1 pages* *DETAIL: 0 index pages have been deleted, 0 are currently reusable.* *CPU 0.00s/0.00u sec elapsed 0.00 sec.* *INFO: analyzing "Orders"*
Regards On Mon, Apr 26, 2010 at 10:55 AM, Bill Moran <wmo...@potentialtech.com>wrote: > In response to akp geek <akpg...@gmail.com>: > > > Hi All - > > > > I have a table bloated with following details > > rows:29431 pages:516039 shouldbe:534 (966.4X) wasted size:4223016960 (3 > GB) > > * > > > > I did a vacuum on the database and also I did vacuumdb > > full on the table. Still there is no change. Can you please suggest if > there > > is any other operation that can be done to take care of the issue > > VACUUM doesn't guarantee that it will clean all the bloat out, it makes > some effort to debloat, but that's not its primary function. > > VACUUM FULL will completely debloat a table, contingent on restrictions > below. Is that what you're running? I'm a little confused by your > comment "vacuumdb full on the table" which contradicts itself. Please > provide the exact commands that your ran, along with the output that > resulted. > > Neither type of VACUUM can debloat rows that are still in use by > transactions. If the applications that connect to this database are > keeping transactions open for long periods, it will adversely affect > those commands' ability to clean up dead rows. > > There is much more here: > http://www.postgresql.org/docs/8.4/static/routine-vacuuming.html > > -- > Bill Moran > http://www.potentialtech.com > http://people.collaborativefusion.com/~wmoran/ >
