X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id ABD4A14B281D for ; Tue, 10 Aug 2004 18:44:05 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 90224-04 for ; Tue, 10 Aug 2004 21:44:01 +0000 (GMT) Received: from web13123.mail.yahoo.com (web13123.mail.yahoo.com [216.136.174.141]) by svr1.postgresql.org (Postfix) with SMTP id 91F9914B27FF for ; Tue, 10 Aug 2004 18:43:58 -0300 (ADT) Message-ID: <20040810214400.55597.qmail@web13123.mail.yahoo.com> Received: from [63.78.248.48] by web13123.mail.yahoo.com via HTTP; Tue, 10 Aug 2004 14:44:00 PDT Date: Tue, 10 Aug 2004 14:44:00 -0700 (PDT) From: Litao Wu Subject: Re: Slow select, insert, update To: Doug McNaught , pgsql-performance@postgresql.org In-Reply-To: <87r7qeg1oe.fsf@asmodeus.mcnaught.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/140 X-Sequence-Number: 7776 Does that mean reindex is not needed for PG version 7.4? In what kind situations under PG 7.4, reindex is worthwhile? Thanks, Here is doc from 7.3: PostgreSQL is unable to reuse B-tree index pages in certain cases. The problem is that if indexed rows are deleted, those index pages can only be reused by rows with similar values. For example, if indexed rows are deleted and newly inserted/updated rows have much higher values, the new rows can't use the index space made available by the deleted rows. Instead, such new rows must be placed on new index pages. In such cases, disk space used by the index will grow indefinitely, even if VACUUM is run frequently. As a solution, you can use the REINDEX command periodically to discard pages used by deleted rows. There is also contrib/reindexdb which can reindex an entire database. The counterpart of 7.4 is: In some situations it is worthwhile to rebuild indexes periodically with the REINDEX command. (There is also contrib/reindexdb which can reindex an entire database.) However, PostgreSQL 7.4 has substantially reduced the need for this activity compared to earlier releases. --- Doug McNaught wrote: > Paul Langard writes: > > > Having trouble with one table (see time to count > records below!). > > > > Fairly new to postgres so any help much > appreciated. > > > > It only contains 9,106 records - as you can see > from: > > > > > > select count(id) from project > > > > count > > 9106 > > 1 row(s) > > Total runtime: 45,778.813 ms > > > ... the database is regularly vaccuumed. > > Hmm. You might try a VACUUM FULL and a REINDEX on > the table (you > don't say what version you are running--REINDEX is > sometimes needed on > 7.3 and below). > > Also, use EXPLAIN ANALYZE on your query and post the > result--that's > helpful diagnostic information. > > -Doug > -- > Let us cross over the river, and rest under the > shade of the trees. > --T. J. Jackson, 1863 > > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend > __________________________________ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail