Обсуждение: reltuples after vacuum and analyze
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
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
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
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
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
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
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