Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 77D419FB51E for ; Mon, 6 Aug 2007 06:51:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 89713-10 for ; Mon, 6 Aug 2007 06:51:19 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from mu-out-0910.google.com (mu-out-0910.google.com [209.85.134.191]) by postgresql.org (Postfix) with ESMTP id 370C99FB6EE for ; Mon, 6 Aug 2007 06:51:28 -0300 (ADT) Received: by mu-out-0910.google.com with SMTP id i10so2001246mue for ; Mon, 06 Aug 2007 02:51:26 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=r0iEwiqPZMr5TEHhrouAQBqv8yau1mph1o1qEW7qipnuGcmJ8q53CWmmj1SwmhXvwWSV9LLjqt/kSdVSdK3H6KYqAL+dMMJPaxG/gwyi3lEUKmwvUKvfQjF2D/c+GaDZtJFZYmCeBZH1Vn6OGuLSLTQWH4F4616Dc2XADzydRKA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=Yah5cw3JTjt4bjkQuVaaqoVgCTPHp67xzJkbQhs9a6Or0fvdFNPY/8woun/E1DQKm7DviWIhPqQk3dowcSVpe8hgP79/1onSZ4xWOm+gFEOdcZJVTcs2WSMQ9tzudTkunFswb4MtZiYe2O3qrRu+PGDoLzQt4B/ytVqhO/QE2II= Received: by 10.86.93.17 with SMTP id q17mr4222958fgb.1186393886482; Mon, 06 Aug 2007 02:51:26 -0700 (PDT) Received: from ?192.168.0.199? ( [80.222.48.97]) by mx.google.com with ESMTPS id p38sm12269297fke.2007.08.06.02.51.20 (version=SSLv3 cipher=RC4-MD5); Mon, 06 Aug 2007 02:51:21 -0700 (PDT) Message-ID: <46B6EF0D.6030501@enterprisedb.com> Date: Mon, 06 Aug 2007 10:51:09 +0100 From: Heikki Linnakangas User-Agent: Icedove 1.5.0.10 (X11/20070329) MIME-Version: 1.0 To: Sven Clement CC: Tom Lane , Andrew Kroeger , Mark Lewis , pgsql-performance@postgresql.org Subject: Re: Performance problems with large telemetric datasets on 7.4.2 References: <881035ea0708030652r37b25a86g61e88e1f5e95eab2@mail.gmail.com> <1186150836.19184.194.camel@archimedes> <881035ea0708030857s621af020r4d3d7b994e2ca9a3@mail.gmail.com> <46B39A77.8050105@sprocks.gotdns.com> <4770.1186180449@sss.pgh.pa.us> <881035ea0708041303h4ea256b3q4b40c5291f8cd3b7@mail.gmail.com> <46B5F5C7.1070901@enterprisedb.com> <881035ea0708060010u1bbb4b58r57bdf6f51b12f7d9@mail.gmail.com> In-Reply-To: <881035ea0708060010u1bbb4b58r57bdf6f51b12f7d9@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/40 X-Sequence-Number: 26170 Sven Clement wrote: > Partially I found that one in the PostgreSQL Documentation for the > 7.x.xversions under the command REINDEX where they claim that you > should run a > reindex under certain circumstances and for my comprehension this says that > with some access pattern (as ours (major writes / one big delete per day)) > the index may be corrupted or otherwise not really useful. Up to 7.3, periodical REINDEX was needed to trim down bloated indexes. Since 7.4, empty index pages are recycled so that's no longer necessary. You can still end up with larger than necessary indexes in recent versions under unusual access patterns, like if you delete all but a few index tuples from each index page, but it's rare in practice. And it's not unbounded growth like in <= 7.3. In any case, the indexes won't become corrupt. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com