pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedreltuples 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>
2005-01-25 00:26 ` Re: reltuples after vacuum and analyze Tom Lane <tgl@sss.pgh.pa.us>
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-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 ` Re: reltuples after vacuum and analyze 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-24 23:02 reltuples after vacuum and analyze Litao Wu <litaowu@yahoo.com>
2005-01-25 00:26 ` Re: reltuples after vacuum and analyze Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-01-25 18:29 ` Litao Wu <litaowu@yahoo.com>
2005-01-25 18:33 ` Re: reltuples after vacuum and analyze 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-24 23:02 reltuples after vacuum and analyze Litao Wu <litaowu@yahoo.com>
2005-01-25 00:26 ` Re: reltuples after vacuum and analyze Tom Lane <tgl@sss.pgh.pa.us>
2005-01-25 18:29 ` Re: reltuples after vacuum and analyze Litao Wu <litaowu@yahoo.com>
@ 2005-01-25 18:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-25 18:41 ` Re: reltuples after vacuum and analyze 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-24 23:02 reltuples after vacuum and analyze Litao Wu <litaowu@yahoo.com>
2005-01-25 00:26 ` Re: reltuples after vacuum and analyze Tom Lane <tgl@sss.pgh.pa.us>
2005-01-25 18:29 ` Re: reltuples after vacuum and analyze Litao Wu <litaowu@yahoo.com>
2005-01-25 18:33 ` Re: reltuples after vacuum and analyze Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-01-25 18:41 ` Litao Wu <litaowu@yahoo.com>
2005-01-25 18:43 ` Re: reltuples after vacuum and analyze 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-24 23:02 reltuples after vacuum and analyze Litao Wu <litaowu@yahoo.com>
2005-01-25 00:26 ` Re: reltuples after vacuum and analyze Tom Lane <tgl@sss.pgh.pa.us>
2005-01-25 18:29 ` Re: reltuples after vacuum and analyze Litao Wu <litaowu@yahoo.com>
2005-01-25 18:33 ` Re: reltuples after vacuum and analyze Tom Lane <tgl@sss.pgh.pa.us>
2005-01-25 18:41 ` Re: reltuples after vacuum and analyze Litao Wu <litaowu@yahoo.com>
@ 2005-01-25 18:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-25 18:48 ` Re: reltuples after vacuum and analyze 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-24 23:02 reltuples after vacuum and analyze Litao Wu <litaowu@yahoo.com>
2005-01-25 00:26 ` Re: reltuples after vacuum and analyze Tom Lane <tgl@sss.pgh.pa.us>
2005-01-25 18:29 ` Re: reltuples after vacuum and analyze Litao Wu <litaowu@yahoo.com>
2005-01-25 18:33 ` Re: reltuples after vacuum and analyze Tom Lane <tgl@sss.pgh.pa.us>
2005-01-25 18:41 ` Re: reltuples after vacuum and analyze Litao Wu <litaowu@yahoo.com>
2005-01-25 18:43 ` Re: reltuples after vacuum and analyze Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-01-25 18:48 ` Litao Wu <litaowu@yahoo.com>
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