X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3AC77533DE for ; Fri, 25 Mar 2005 22:36:08 +0000 (GMT) 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 88833-02 for ; Fri, 25 Mar 2005 22:35:57 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id F0D7F533D9 for ; Fri, 25 Mar 2005 22:35:56 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2PMZwrg014074; Fri, 25 Mar 2005 17:35:58 -0500 (EST) To: Simon Riggs Cc: "Matthew T. O'Connor" , pgsql-hackers@postgreSQL.org, Otto Blomqvist , pgsql-performance@postgreSQL.org Subject: Re: lazy_update_relstats considered harmful (was Re: [PERFORM] pg_autovacuum not having enough suction ?) In-reply-to: <1111789223.11750.839.camel@localhost.localdomain> References: <42430FA0.9080204@zeut.net> <002b01c530c5$ad5a1620$e602a8c0@blomqvist> <42435408.30303@zeut.net> <00f201c53168$96903480$e602a8c0@blomqvist> <42446A66.6010504@zeut.net> <11991.1111780527@sss.pgh.pa.us> <12121.1111782169@sss.pgh.pa.us> <1111789223.11750.839.camel@localhost.localdomain> Comments: In-reply-to Simon Riggs message dated "Fri, 25 Mar 2005 22:20:23 +0000" Date: Fri, 25 Mar 2005 17:35:58 -0500 Message-ID: <14073.1111790158@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/913 X-Sequence-Number: 65715 Simon Riggs writes: > On Fri, 2005-03-25 at 15:22 -0500, Tom Lane wrote: >> 2. Dead tuples don't have that much influence on scan costs either, at >> least not once they are marked as known-dead. Certainly they shouldn't >> be charged at full freight. > Yes, minor additional CPU time, but the main issue is when the dead > tuples force additional I/O. I/O costs are mostly estimated off relpages, though, not reltuples. The only time you really pay through the nose for a dead tuple is when an indexscan visits it, but with the known-dead marking we now do in btree indexes, I'm pretty sure that path is seldom taken. >> It's possible that there'd be some value in adding a column to pg_class >> to record dead tuple count, but given what we have now, the calculation >> in lazy_update_relstats is totally wrong. > Yes, thats the way. We can record the (averaged?) dead tuple count, but > also record the actual row count in reltuples. What I'd be inclined to record is the actual number of dead rows removed by the most recent VACUUM. Any math on that is best done in the planner, since we can change the logic more easily than the database contents. It'd probably be reasonable to take half of that number as the estimate of the average number of dead tuples. But in any case, that's for the future; we can't have it in 8.0.*, and right at the moment I'm focusing on what to push out for 8.0.2. > We definitely need to record the physical and logical tuple counts, > since each of them have different contributions to run-times. There isn't any difference, if you are talking about fully dead tuples. It would be possible for VACUUM to also count the number of not-committed-but-not-removable tuples (ie, new from still-open transactions, plus dead-but-still-visible-to-somebody), but I'm not sure that it would be useful to do so, because that sort of count is hugely transient. The stat would be irrelevant moments after it was taken. regards, tom lane