Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 3E2A364FD82
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 28 Nov 2008 04:30:59 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 26407-09
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 28 Nov 2008 04:30:51 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mx.inode.at (mx03.lb01.inode.at [62.99.145.3])
	by mail.postgresql.org (Postfix) with ESMTP id 3612664FD44
	for <pgsql-performance@postgresql.org>;
	Fri, 28 Nov 2008 04:30:50 -0400 (AST)
Received: from [85.124.56.44] (port=13390 helo=[10.192.17.33])
	by smartmx-03.inode.at with esmtp (Exim 4.50)
	id 1L5ykP-0000a3-MF; Fri, 28 Nov 2008 09:30:45 +0100
Message-ID: <492FAC30.7080409@sime.com>
Date: Fri, 28 Nov 2008 09:30:40 +0100
From: Mario Weilguni <mweilguni@sime.com>
User-Agent: Thunderbird 2.0.0.18 (X11/20081125)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: vegard.bones@met.no, pgsql-performance@postgresql.org
Subject: Re: Deteriorating performance when loading large objects
References: <63696.85.166.86.82.1227617663.squirrel@webmailsunny.oslo.dnmi.no>
	<14976.1227620206@sss.pgh.pa.us>
	<49362.85.166.86.82.1227812685.squirrel@webmailsunny.oslo.dnmi.no>
	<11058.1227821378@sss.pgh.pa.us>
In-Reply-To: <11058.1227821378@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200811/288
X-Sequence-Number: 32019

Tom Lane schrieb:
> "=?iso-8859-1?Q?Vegard_B=F8nes?=" <vegard.bones@met.no> writes:
>   
>> Running VACUUM VERBOSE pg_largeobject took quite some time. Here's the
>> output:
>>     
>
>   
>> INFO:  vacuuming "pg_catalog.pg_largeobject"
>> INFO:  index "pg_largeobject_loid_pn_index" now contains 11060658 row
>> versions in 230587 pages
>> DETAIL:  178683 index pages have been deleted, 80875 are currently reusable.
>> CPU 0.92s/0.10u sec elapsed 199.38 sec.
>> INFO:  "pg_largeobject": found 0 removable, 11060658 nonremovable row
>> versions in 6849398 pages
>> DETAIL:  0 dead row versions cannot be removed yet.
>> There were 84508215 unused item pointers.
>> 0 pages are entirely empty.
>> CPU 0.98s/0.10u sec elapsed 4421.17 sec.
>> VACUUM
>>     
>
> Hmm ... although you have no dead rows now, the very large number of
> unused item pointers suggests that there were times in the past when
> pg_largeobject didn't get vacuumed often enough.  You need to look at
> your vacuuming policy.  If you're using autovacuum, it might need to have
> its parameters adjusted.  Otherwise, how often are you vacuuming, and
> are you doing it as superuser?
>
>   
>> I will try to run VACUUM ANALYZE FULL after the next delete tonight, as
>> suggested by Ivan Voras in another post.
>>     
>
> Actually, a CLUSTER might be more effective.
>
> 			regards, tom lane
>
>   

Does CLUSTER really help here? On my 8.2 database, I get:
CLUSTER pg_largeobject_loid_pn_index on pg_largeobject ;
ERROR:  "pg_largeobject" is a system catalog


Has this changed in >= 8.3?