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 968695334B for ; Mon, 18 Apr 2005 16:21:54 -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 46429-09 for ; Mon, 18 Apr 2005 19:21:45 +0000 (GMT) Received: from web51405.mail.yahoo.com (web51405.mail.yahoo.com [206.190.38.184]) by svr1.postgresql.org (Postfix) with SMTP id 86E5A5339D for ; Mon, 18 Apr 2005 16:21:41 -0300 (ADT) Received: (qmail 84421 invoked by uid 60001); 18 Apr 2005 19:21:42 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=4QK53IGFPF6fKgdpF/c0c4XpvV8MSMwYss7nJ6+E8oaZ0LBSMqykup0RR7ZXcd8j/jZ8C3229YF2U3z4kBp21jXuPm2sLM36346G66CdWzTW5RKJNU3hdyBkuqtCpUx13QgcK6nyFb82VVvedJJVaD+MZsB5VgNOTeazXxGzyi4= ; Message-ID: <20050418192142.84419.qmail@web51405.mail.yahoo.com> Received: from [192.88.67.254] by web51405.mail.yahoo.com via HTTP; Mon, 18 Apr 2005 12:21:42 PDT Date: Mon, 18 Apr 2005 12:21:42 -0700 (PDT) From: Bill Chandler Subject: Question on REINDEX To: pgsql-perform 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.846 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, FROM_ENDS_IN_NUMS X-Spam-Level: X-Archive-Number: 200504/430 X-Sequence-Number: 11827 All, A couple of questions regarding REINDEX command: Running PostgreSQL 7.4.2 on Solaris. 1) When is it necessary to run REINDEX or drop/create an index? All I could really find in the docs 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." What are these situations? We have a database with some large tables. Currently we reindex (actually drop/create) nightly. But as the tables have grown this has become prohibitively time-consuming. According to the above comment it may not be necessary at all. 2) If reindexing is necessary, how can this be done in a non-obtrusive way in a production environment. Our database is being updated constantly. REINDEX locks client apps out while in progress. Same with "CREATE INDEX" when we drop/create. The table can have over 10 million row. Recreating the indexes seems to take hours. This is too long to lock the client apps out. Is there any other solution? thanks, Bill __________________________________ Do you Yahoo!? Make Yahoo! your home page http://www.yahoo.com/r/hs