From: Litao Wu <litaowu@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: reltuples after vacuum and analyze
Date: Mon, 24 Jan 2005 15:02:42 -0800 (PST)
Message-ID: <20050124230242.18329.qmail@web30304.mail.mud.yahoo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: litaowu@yahoo.com
Subject: Re: reltuples after vacuum and analyze
In-Reply-To: <20050124230242.18329.qmail@web30304.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox