On 11/4/07 8:26 PM, "Tom Lane" <tgl@xxxxxxxxxxxxx> wrote: > "Michael Goldner" <MGoldner@xxxxxxxxxxxx> writes: >> I have a database with a single table that includes an oid reference to a >> large object. After loading 100GB of large objects using lo_import(), I >> find that my total database size has grown by about 270GB. What is the >> reason for the difference in space usage? > > Try VACUUM VERBOSE on your table and also on pg_largeobject to get a > sense of where the space went. I'm wondering whether you allowed for > indexes and for other data in your table. > > regards, tom lane OK, I vacuumed both tables. The single table vacuumed in about 2 seconds and the stats look normal: INFO: vacuuming "public.image" INFO: index "image_pkey" now contains 386749 row versions in 1691 pages DETAIL: 21799 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.10u sec elapsed 0.14 sec. INFO: "image": removed 21799 row versions in 716 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "image": found 21799 removable, 386749 nonremovable row versions in 19589 pages DETAIL: 0 dead row versions cannot be removed yet. There were 183153 unused item pointers. 0 pages are entirely empty. CPU 0.09s/0.19u sec elapsed 0.32 sec. INFO: vacuuming "pg_toast.pg_toast_176283637" INFO: index "pg_toast_176283637_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: "pg_toast_176283637": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM The pg_largeobject table, however, seems a bit odd: INFO: vacuuming "pg_catalog.pg_largeobject" INFO: index "pg_largeobject_loid_pn_index" now contains 105110204 row versions in 404151 pages DETAIL: 778599 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 21.24s/48.07u sec elapsed 273.15 sec. INFO: "pg_largeobject": removed 778599 row versions in 775264 pages DETAIL: CPU 54.73s/29.70u sec elapsed 2203.32 sec. INFO: "pg_largeobject": found 778599 removable, 105094846 nonremovable row versions in 34803136 pages DETAIL: 0 dead row versions cannot be removed yet. There were 70 unused item pointers. 0 pages are entirely empty. CPU 1031.40s/339.21u sec elapsed 10875.66 sec. VACUUM In particular, "105094846 nonremovable row versions in 34803136 pages" seems really high given that there are only about 400,000 large objects. The majority of the lobs are less than 1MB. However, a small subset can range as high as 1GB. I see that a single object is spread across multiple pages (loid, pageno) in the pg_largeobject table. Is it necessary or possible to adjust the page size for greater efficiency? Thanks, Mike ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
