X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AC2E43A5807 for ; Tue, 25 Jan 2005 18:29:40 +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 01090-09 for ; Tue, 25 Jan 2005 18:29:29 +0000 (GMT) Received: from web30309.mail.mud.yahoo.com (web30309.mail.mud.yahoo.com [68.142.200.102]) by svr1.postgresql.org (Postfix) with SMTP id 46ECA3A57EE for ; Tue, 25 Jan 2005 18:29:27 +0000 (GMT) Received: (qmail 98346 invoked by uid 60001); 25 Jan 2005 18:29:26 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=jgwgnHjbudMFUN/MQ+t/EgNy8vA2CjGZHkCQaEda8PgNpIELWsLeEMjwny6A8Vr3tz3AehqWcnaqpez9QjUcyZpP2vcMZPhaswSx5dQ4SUAczQOOY7QVVHWfuuXumqoFW3+JGs4QDbnmOR2y7dd//yVcOcIRL2sCYwCIve2aimI= ; Message-ID: <20050125182926.98344.qmail@web30309.mail.mud.yahoo.com> Received: from [63.78.249.254] by web30309.mail.mud.yahoo.com via HTTP; Tue, 25 Jan 2005 10:29:26 PST Date: Tue, 25 Jan 2005 10:29:26 -0800 (PST) From: Litao Wu Subject: Re: reltuples after vacuum and analyze To: Tom Lane Cc: pgsql-performance@postgresql.org In-Reply-To: <27553.1106612786@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.639 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, FORGED_YAHOO_RCVD X-Spam-Level: * X-Archive-Number: 200501/479 X-Sequence-Number: 10169 Thanks, Then how to explain relpages (size_kb in result returned)? SELECT relname, relpages * 8 as size_kb, relfilenode, reltuples FROM pg_class c1 WHERE relkind = 'r' AND relname = 'my_tab'; relname | size_kb | relfilenode | reltuples ------------------+---------+-------------+----------- my_tab | 30088 | 266181583 | 165724 analyze my_tab; relname | size_kb | relfilenode | reltuples ------------------+---------+-------------+------------- my_tab | 2023024 | 266181583 | 1.12323e+07 vacuum my_tab; SELECT relname, relpages * 8 as size_kb, relfilenode, reltuples FROM pg_class c1 WHERE relkind = 'r' AND relname = 'my_tab'; relname | size_kb | relfilenode | reltuples ------------------+---------+-------------+----------- my_tab | 2038016 | 266181583 | 189165 (1 row) --- Tom Lane wrote: > Litao Wu writes: > > I noticed that reltuples are way off if > > I vacuum the table and analyze the table. > > And the data (296901) after vacuum seems > > accurate while > > the reltuples (1.90744e+06) > > after anlayze is too wrong. > > VACUUM derives an exact count because it scans the > whole table. ANALYZE > samples just a subset of the table and extrapolates. > It would appear > that you've got radically different tuple densities > in different parts > of the table, and that's confusing ANALYZE. > > > My PG version is 7.3.2 (I know it is old). > > 8.0's ANALYZE uses a new sampling method that we > think is less prone > to this error, though of course any sampling method > will fail some of > the time. > > regards, tom lane > __________________________________ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250