X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A6C7ED1B534 for ; Mon, 24 May 2004 16:48:26 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 85061-02 for ; Mon, 24 May 2004 16:48:04 -0300 (ADT) Received: from web13125.mail.yahoo.com (web13125.mail.yahoo.com [216.136.174.143]) by svr1.postgresql.org (Postfix) with SMTP id E1537D1B276 for ; Mon, 24 May 2004 16:48:01 -0300 (ADT) Message-ID: <20040524194803.8970.qmail@web13125.mail.yahoo.com> Received: from [63.78.248.48] by web13125.mail.yahoo.com via HTTP; Mon, 24 May 2004 12:48:03 PDT Date: Mon, 24 May 2004 12:48:03 -0700 (PDT) From: Litao Wu Subject: Re: index's relpages after table analyzed To: josh@agliodbs.com, pgsql-performance@postgresql.org In-Reply-To: <200405241205.26170.josh@agliodbs.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/201 X-Sequence-Number: 7032 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 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/