X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1F9A7DB103 for ; Thu, 27 Oct 2005 19:09:47 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 54954-10 for ; Thu, 27 Oct 2005 22:09:44 +0000 (GMT) Received: from h1.bettercom.de (h1.bettercom.de [213.239.194.41]) by svr1.postgresql.org (Postfix) with ESMTP id 2A35DDB113 for ; Thu, 27 Oct 2005 19:09:44 -0300 (ADT) Received: (qmail 3978 invoked by uid 208); 27 Oct 2005 22:09:47 -0000 Received: from 213.39.214.61 by h1 (envelope-from , uid 0) with qmail-scanner-1.23 (clamdscan: 0.87. spamassassin: 3.04. Clear:RC:1(213.39.214.61):. Processed in 0.034149 secs); 27 Oct 2005 22:09:47 -0000 Received: from unknown (HELO nb-aspire.bettercom.de) ([213.39.214.61]) (envelope-sender ) by h1.bettercom.de (qmail-ldap-1.03) with SMTP for ; 27 Oct 2005 22:09:47 -0000 Received: (nullmailer pid 10451 invoked by uid 1000); Thu, 27 Oct 2005 22:09:46 -0000 Subject: Re: Reasons and drawbacks for unused item pointers X-PGP-Key: http://bettercom.de/misc/3A547DE6.asc X-Face: "d[&>8')a)wbF:+L#^<_cohnX6#m5RCCeKF/6_gD(iQ9bX?xe2~Aq*!')D(1ks`?YhomOYbL3R:{4e4a]qft_]<.q/Lf4hIr,`G+LX33&TYp}XGfe~^5m$28R"$C(EwnB\n8t In-Reply-To: <24260.1130448996@sss.pgh.pa.us> (Tom Lane's message of "Thu, 27 Oct 2005 17:36:36 -0400") References: <19722.1130429883@sss.pgh.pa.us> <23322.1130442063@sss.pgh.pa.us> <87y84eznb0.fsf_-_@nb-aspire.bettercom.de> <24260.1130448996@sss.pgh.pa.us> To: pgsql-performance@postgresql.org From: Martin Lesser Date: Fri, 28 Oct 2005 00:09:46 +0200 Message-ID: <87u0f2zk5x.fsf@nb-aspire.bettercom.de> User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4 (Jumbo Shrimp, linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.005 required=5 tests=[AWL=0.005] X-Spam-Score: 0.005 X-Spam-Level: X-Archive-Number: 200510/433 X-Sequence-Number: 15201 Tom Lane writes: > Martin Lesser writes: >> What causes this "unused item pointers" and which impact do they have >> regarding performance? > The direct performance impact is really pretty minimal (and none at > all on indexscans, AFAIR). The reason Denis' number drew my attention > was that it implied that the table had gone un-vacuumed for awhile at > some time in the past. [...] To have 1905028 unused pointers in a > table with only 5106307 live entries suggests that at some point there > were 1.9 million (or so) dead but not-yet-vacuumed tuples, which > suggests insufficient vacuuming. Does each update of a single row result in an "unused item pointer"? I.e. if I update one row 10 times between VACUUMing the table the result are 10 unused pointers? Some rows in some of my tables are updated much more frequently than others so I'm not sure whether the number of unused pointers implie that I should VACUUM more often than every 24 hours. Martin