Skip site navigation (1) Skip section navigation (2)

reltuples after vacuum and analyze

From: Litao Wu <litaowu(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: reltuples after vacuum and analyze
Date: 2005-01-24 23:02:42
Message-ID: 20050124230242.18329.qmail@web30304.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2005-01-24 23:45:51
Subject: Re: PgPool changes WAS: PostgreSQL clustering VS MySQL
Previous:From: Alexander DolginDate: 2005-01-24 18:33:39
Subject: 200 times slower then MSSQL??

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group