pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (7+ messages)  latest in thread

Message-ID: <20050124230242.18329.qmail@web30304.mail.mud.yahoo.com>
Permalink:  ../20050124230242.18329.qmail@web30304.mail.mud.yahoo.com/
Also on:    postgresql.org/message-id/20050124230242.18329.qmail@web30304.mail.mud.yahoo.com

 · 

reply

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