Re: [PERFORM] index's relpages after table analyzed
Litao Wu <[EMAIL PROTECTED]> writes: > My question is why relpages of indexes > do not get updated after "analyze". It's an oversight, which just got fixed in CVS tip a few weeks ago. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] index's relpages after table analyzed
Hi Josh,
I know that and that is what I am using now.
The problem is I also need to know
the relpages each indexe takes and "analyze"
seems not update relpages though vacuum and
vacuum analyze do.
According to PG doc:
"Remember, relpages is only updated by VACUUM and
ANALYZE"
My question is why relpages of indexes
do not get updated after "analyze".
Here is a quick test:
create table test as select * from pg_class where 1=2;
create index test_idx on test (relname);
insert into test select * from pg_class;
select relname, relpages from pg_class
where relname in ('test', 'test_idx');
relname | relpages
--+--
test | 10
test_idx |1
(2 rows)
analyze test;
select relname, relpages from pg_class
where relname in ('test', 'test_idx');
relname | relpages
--+--
test | 27
test_idx |1
(2 rows)
-- Analyze only updates table's relpage, not index's!
vacuum analyze test;
select relname, relpages from pg_class
where relname in ('test', 'test_idx');
relname | relpages
--+--
test | 27
test_idx | 22
(2 rows)
-- "acuum analzye" updates both
-- "vacuum" only also updates both
Thank you for your help!
--- Josh Berkus <[EMAIL PROTECTED]> wrote:
> Litao,
>
> > I have some tables and there are almost only
> > inserts. So I do not care about the "dead tuples",
> > but do care about the statistics.
>
> Then just run ANALYZE on those tables, and not
> VACUUM.
> ANALYZE ;
>
> > My PG version is 7.3.2.
>
> I would suggest upgrading to 7.3.6; the version you
> are using has several
> known bugs.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
__
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PERFORM] index's relpages after table analyzed
Litao, > I have some tables and there are almost only > inserts. So I do not care about the "dead tuples", > but do care about the statistics. Then just run ANALYZE on those tables, and not VACUUM. ANALYZE ; > My PG version is 7.3.2. I would suggest upgrading to 7.3.6; the version you are using has several known bugs. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] index's relpages after table analyzed
>From PG http://developer.postgresql.org/docs/postgres/diskusage.html: "(Remember, relpages is only updated by VACUUM and ANALYZE.)" --- Litao Wu <[EMAIL PROTECTED]> wrote: > Hi, > > After a table analyzed a table, the table's relpages > > of pg_class gets updated, but not those of > associated > indexes, which can be updated by "vacuum analyze". > > Is this a feature or a bug? > > I have some tables and there are almost only > inserts. So I do not care about the "dead tuples", > but do care about the statistics. > > Does the above "future/bug" affect the performance? > > My PG version is 7.3.2. > > Thanks, > > > > > > > __ > Do you Yahoo!? > Yahoo! Domains ?Claim yours for only $14.70/year > http://smallbusiness.promotions.yahoo.com/offer > > ---(end of > broadcast)--- > TIP 8: explain analyze is your friend __ Do you Yahoo!? Yahoo! Domains Claim yours for only $14.70/year http://smallbusiness.promotions.yahoo.com/offer ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
