pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
reltuples after vacuum and analyze
7+ messages / 2 participants
[nested] [flat]

* reltuples after vacuum and analyze
@ 2005-01-24 23:02  Litao Wu <litaowu@yahoo.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Litao Wu @ 2005-01-24 23:02 UTC (permalink / raw)
  To: 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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: reltuples after vacuum and analyze
@ 2005-01-25 00:26  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Litao Wu <litaowu@yahoo.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2005-01-25 00:26 UTC (permalink / raw)
  To: Litao Wu <litaowu@yahoo.com>; +Cc: pgsql-performance

Litao Wu <litaowu@yahoo.com> 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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: reltuples after vacuum and analyze
@ 2005-01-25 18:29  Litao Wu <litaowu@yahoo.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Litao Wu @ 2005-01-25 18:29 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

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 <tgl@sss.pgh.pa.us> wrote:

> Litao Wu <litaowu@yahoo.com> 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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: reltuples after vacuum and analyze
@ 2005-01-25 18:33  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Litao Wu <litaowu@yahoo.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2005-01-25 18:33 UTC (permalink / raw)
  To: Litao Wu <litaowu@yahoo.com>; +Cc: pgsql-performance

Litao Wu <litaowu@yahoo.com> writes:
> Then how to explain relpages 
> (size_kb in result returned)?

relpages should be accurate in either case, since we get that by asking
the kernel (lseek).

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: reltuples after vacuum and analyze
@ 2005-01-25 18:41  Litao Wu <litaowu@yahoo.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Litao Wu @ 2005-01-25 18:41 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

I know it is accurate.
My question is why the table takes 
2023024KB after analyzed?
And why it does not shink to 30088 after vacuumed?

I know "vacuum full verbose"
will force it shrink to
reasonable size. But I do not understand
why "analyze" bloats the table size so 
big??

Please note all above commands are done within
minutes and I truely do not believe the table
of 189165 rows takes that much space.

Furthermore, I notice last weekly "vacuum full"
even did not reclaim the space back.

Thanks,

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Litao Wu <litaowu@yahoo.com> writes:
> > Then how to explain relpages 
> > (size_kb in result returned)?
> 
> relpages should be accurate in either case, since we
> get that by asking
> the kernel (lseek).
> 
> 			regards, tom lane
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: reltuples after vacuum and analyze
@ 2005-01-25 18:43  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Litao Wu <litaowu@yahoo.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2005-01-25 18:43 UTC (permalink / raw)
  To: Litao Wu <litaowu@yahoo.com>; +Cc: pgsql-performance

Litao Wu <litaowu@yahoo.com> writes:
> reasonable size. But I do not understand
> why "analyze" bloats the table size so 
> big??

ANALYZE won't bloat anything.  I suppose you have other processes
inserting or updating data in the table meanwhile.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: reltuples after vacuum and analyze
@ 2005-01-25 18:48  Litao Wu <litaowu@yahoo.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 7+ messages in thread

From: Litao Wu @ 2005-01-25 18:48 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Believe or not.
The above command is my screen snapshot.

I believe it is most possibably a PG bug!

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Litao Wu <litaowu@yahoo.com> writes:
> > reasonable size. But I do not understand
> > why "analyze" bloats the table size so 
> > big??
> 
> ANALYZE won't bloat anything.  I suppose you have
> other processes
> inserting or updating data in the table meanwhile.
> 
> 			regards, tom lane
> 



		
__________________________________ 
Do you Yahoo!? 
All your favorites on one personal page – Try My Yahoo!
http://my.yahoo.com 



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2005-01-25 18:48 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-24 23:02 reltuples after vacuum and analyze Litao Wu <litaowu@yahoo.com>
2005-01-25 00:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-25 18:29   ` Litao Wu <litaowu@yahoo.com>
2005-01-25 18:33     ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-25 18:41       ` Litao Wu <litaowu@yahoo.com>
2005-01-25 18:43         ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-25 18:48           ` Litao Wu <litaowu@yahoo.com>

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