X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3129D528A2 for ; Wed, 18 May 2005 16:41:01 -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 49503-08 for ; Wed, 18 May 2005 19:40:56 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id D8520528A5 for ; Wed, 18 May 2005 16:40:55 -0300 (ADT) 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 j4IJetrD024305; Wed, 18 May 2005 15:40:55 -0400 (EDT) To: Jeff Boes Cc: pgsql-admin@postgresql.org Subject: Re: Sidetracking pg_autovacuum In-reply-to: <428B8A27.7050402@nexcerpt.com> References: <428B8A27.7050402@nexcerpt.com> Comments: In-reply-to Jeff Boes message dated "Wed, 18 May 2005 14:32:07 -0400" Date: Wed, 18 May 2005 15:40:55 -0400 Message-ID: <24304.1116445255@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/248 X-Sequence-Number: 17677 Jeff Boes writes: > What would cause an otherwise well-behaved table to start doing this? Is > this just a "dead spot" in the ANALYZE command? (By which I mean: > ANALYZE randomly sampling rows, but my data is not terribly random, so > it gets fooled?) 7.4's ANALYZE is indeed easily fooled by nonuniform tuple density. 8.0 uses a different sampling algorithm that we hope will do better. > [And here's the remaining question in my puzzled mind: ANALYZE would not > change the reltuples value, but VACUUM FULL ANALYZE did. Er-wha?] Any variant of VACUUM records the exact number of tuples that it found in its full table scan in reltuples. It's only a standalone ANALYZE that has to approximate. The case where you saw reltuples > count(*) probably came from the fact that VACUUM counts physical tuples --- that is, the difference reflects dead-but-not-yet-removable tuples. regards, tom lane