Thread: reltuples after vacuum and analyze

reltuples after vacuum and analyze

From

Litao Wu

Date:

25 January 2005, 03:15:33

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

Re: reltuples after vacuum and analyze

From

Tom Lane

Date:

25 January 2005, 03:52:37

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

Re: reltuples after vacuum and analyze

From

Litao Wu

Date:

25 January 2005, 21:29:47

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

Re: reltuples after vacuum and analyze

From

Tom Lane

Date:

25 January 2005, 21:33:11

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

Re: reltuples after vacuum and analyze

From

Tom Lane

Date:

25 January 2005, 21:46:58

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

Re: reltuples after vacuum and analyze

From

Litao Wu

Date:

25 January 2005, 21:48:51

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

Re: reltuples after vacuum and analyze

From

Litao Wu

Date:

25 January 2005, 21:50:47

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