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 91E3F3A45DE for ; Mon, 24 Jan 2005 23:38:48 +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 04511-03 for ; Mon, 24 Jan 2005 23:38:41 +0000 (GMT) Received: by svr1.postgresql.org (Postfix, from userid 1001) id A370C3A5BF1; Tue, 25 Jan 2005 00:32:44 +0000 (GMT) Received: from web30304.mail.mud.yahoo.com (web30304.mail.mud.yahoo.com [68.142.200.97]) by svr1.postgresql.org (Postfix) with SMTP id D89A83A5647 for ; Mon, 24 Jan 2005 23:02:42 +0000 (GMT) Received: (qmail 18331 invoked by uid 60001); 24 Jan 2005 23:02:42 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=ic9UIq6Xv2rhStph8dFvI8tC+g059Rq77LN/0UnnoDWojvLy6wdrpwcKCCGx5CM83ppj0pErFLd8VcpH60fiduGaJwLPuA+9d/Ld1WAjppUd3uIkRshJyu1qq/YyKxx3ZzBzhrBCNZdZld2BOprxGuSL8oCLP21TrsYrXZLUvjQ= ; Message-ID: <20050124230242.18329.qmail@web30304.mail.mud.yahoo.com> Received: from [63.78.249.254] by web30304.mail.mud.yahoo.com via HTTP; Mon, 24 Jan 2005 15:02:42 PST Date: Mon, 24 Jan 2005 15:02:42 -0800 (PST) From: Litao Wu Subject: reltuples after vacuum and analyze To: pgsql-performance@postgresql.org 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.367 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, FORGED_YAHOO_RCVD X-Spam-Level: * X-Archive-Number: 200501/445 X-Sequence-Number: 10135 Hi, 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. My PG version is 7.3.2 (I know it is old). Any thought? Thanks, my_db=# analyze my_tab; ANALYZE my_db=# SELECT relname, relpages * 8 as size_kb, relfilenode, reltuples my_db=# FROM pg_class c1 my_db=# WHERE relkind = 'r' my_db=# AND relname = 'my_tab'; relname | size_kb | relfilenode | reltuples ------------------+---------+-------------+------------- my_tab | 394952 | 211002264 | 1.90744e+06 (1 row) my_db=# select count(*) from my_tab; count -------- 296694 (1 row) my_db=# vacuum verbose my_tab; INFO: --Relation public.my_tab-- INFO: Index my_tab_pkey: Pages 5909; Tuples 296901: Deleted 6921. CPU 0.20s/0.19u sec elapsed 4.76 sec. INFO: Index my_tab_hid_state_idx: Pages 5835; Tuples 297808: Deleted 6921. CPU 0.17s/0.07u sec elapsed 9.62 sec. INFO: Removed 6921 tuples in 310 pages. CPU 0.00s/0.01u sec elapsed 0.08 sec. INFO: Pages 49369: Changed 12, Empty 0; Tup 296901: Vac 6921, Keep 0, UnUsed 1431662. Total CPU 1.71s/0.47u sec elapsed 28.48 sec. VACUUM my_db=# SELECT relname, relpages * 8 as size_kb, relfilenode, reltuples my_db=# FROM pg_class c1 my_db=# WHERE relkind = 'r' my_db=# AND relname = 'my_tab'; relname | size_kb | relfilenode | reltuples ------------------+---------+-------------+----------- my_tab | 394952 | 211002264 | 296901 (1 row) my_db=# analyze my_tab; ANALYZE my_db=# SELECT relname, relpages * 8 as size_kb, relfilenode, reltuples my_db=# FROM pg_class c1 my_db=# WHERE relkind = 'r' my_db=# AND relname = 'my_tab'; relname | size_kb | relfilenode | reltuples ------------------+---------+-------------+------------- my_tab | 394952 | 211002264 | 1.90744e+06 (1 row) __________________________________ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail