But as you could see from the prior query \lo_list showed no large
objects, this was done just prior to the vacuum.
aesop=# \lo_list
Large objects
ID | Description
----+-------------
(0 rows)
aesop=# vacuum verbose pg_largeobject;
NOTICE: --Relation pg_largeobject--
NOTICE: Index pg_largeobject_loid_pn_index: Pages 2819; Tuples 460:
Deleted 84.
CPU 0.21s/0.02u sec elapsed 0.23 sec.
NOTICE: Removed 84 tuples in 19 pages.
CPU 0.01s/0.01u sec elapsed 0.01 sec.
NOTICE: Pages 19: Changed 0, Empty 0; Tup 460: Vac 84, Keep 460, UnUsed
2.
Total CPU 0.22s/0.03u sec elapsed 0.25 sec.
VACUUM
I am using the JDBC LargeObject.delete() method to remove large objects
from the pg_largeobject table. Could you suggest a better mechanism to
use from java?
Chris
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, October 06, 2003 8:53 PM
To: cjwhite@cisco.com
Cc: 'Robert Treat'; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Question about DB VACUUM
"Chris White \(cjwhite\)" <cjwhite@cisco.com> writes:
> Why aren't there any unused tuples?
The "unused" number isn't especially interesting, it's just the number
of line pointer slots that were once used and aren't at the moment. At 4
bytes apiece, they aren't costing you anything worth noticing.
> Why is the pg_largeobject_loid_pn_index table so big (2818 pages)?
This looks like a standard "index bloat" problem (see the archives for
details). "REINDEX pg_largeobject" would make the bloat go away for
awhile. 7.4 should largely solve this problem, but in earlier releases
you need to figure on periodic reindexing.
> Why has table grown by 4 pages.
Probably because there are now 460 live tuples instead of 227. I don't
think you've entirely fixed your problem of not removing all unused
large objects...
regards, tom lane