Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 3BA106348F3 for ; Wed, 31 Mar 2010 18:34:24 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 01082-01-4 for ; Wed, 31 Mar 2010 21:34:08 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pz0-f172.google.com (mail-pz0-f172.google.com [209.85.222.172]) by mail.postgresql.org (Postfix) with ESMTP id 7EBC96348C0 for ; Wed, 31 Mar 2010 18:33:50 -0300 (ADT) Received: by pzk2 with SMTP id 2so578093pzk.19 for ; Wed, 31 Mar 2010 14:33:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:received:message-id:subject:from:to:cc:content-type; bh=6VTIEncOKAhaeRDhDr50Jg1KG6tO6aToocVR6I+x0Fw=; b=J2BdqnSvyHusn70J02Ih3n4mDZYTH5LqPsmfOBAHuftfoouGVAjM9NTKM7FjT+A/xP 6wELSNevd8OKJ0OcMBZoiaSiEQlN0Iq+g2p35r5wjDbAsJw54PeTPB8U38doTALJpKDq M/hN9RQrn2dG0OBQhJinDR0ItqHm7WDCwwOyY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=rjuvp37jrccV+fzwQvUQqJuaplwXOYf0S/D2sphYDSUrvnlhWMTX+V6JTgVcboJz1Q hyZHjWv+vLnF30zIisb87ZShMwBHZZZ521+oj8kEsw6DXdWawzZVcoT0JvtGordZ7+Bj uRcgUF19cYM7SVUpvKrlMcrUYt68QfftRW1tM= MIME-Version: 1.0 Received: by 10.140.178.4 with HTTP; Wed, 31 Mar 2010 14:33:48 -0700 (PDT) In-Reply-To: <4BB3722F02000025000302BF@gw.wicourts.gov> References: <4BB3722F02000025000302BF@gw.wicourts.gov> Date: Thu, 1 Apr 2010 03:03:48 +0530 Received: by 10.141.12.6 with SMTP id p6mr1354383rvi.195.1270071228612; Wed, 31 Mar 2010 14:33:48 -0700 (PDT) Message-ID: Subject: Re: How to fast the REINDEX From: raghavendra t To: Kevin Grittner Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd109526bc12504831f8011 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/417 X-Sequence-Number: 38242 --000e0cd109526bc12504831f8011 Content-Type: text/plain; charset=ISO-8859-1 Hi Kevin, Thank you for the update, >>What does the table look like? What indexes are there? Table has a combination of byteas. Indexes are b-tree and Partial >>Why are you doing that? Our table face lot of updates and deletes in a day, so we prefer reindex to update the indexes as well overcome with a corrupted index. >> How long? More than 4 hrs.. >>What run time are you expecting? Less than what it is taking at present. >>It's hard to answer that without more information, like PostgreSQL >>version and configuration, for starters. See: version ------------------------------------------------------------------------------------------------------------ PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-44), 32-bit (1 row) >>http://wiki.postgresql.org/wiki/SlowQueryQuestions Expected the performance question.. Regards Raghavendra On Thu, Apr 1, 2010 at 2:32 AM, Kevin Grittner wrote: > raghavendra t wrote: > > > I have a table with 40GB size, it has few indexes on it. > > What does the table look like? What indexes are there? > > > When i try to REINDEX on the table, > > Why are you doing that? > > > its take a long time. > > How long? > > > I tried increasing the maintenance_work_mem, but still i havnt > > find a satisfying result. > > What run time are you expecting? > > > Questions > > ======= > > 1. What are the parameters will effect, when issuing the REINDEX > > command > > 2. Best possible way to increase the spead of the REINDEX > > It's hard to answer that without more information, like PostgreSQL > version and configuration, for starters. See: > > http://wiki.postgresql.org/wiki/SlowQueryQuestions > > My best guess is that you can make them instantaneous by not running > them. A good VACUUM policy should make such runs unnecessary in > most cases -- at least on recent PostgreSQL versions. > > -Kevin > --000e0cd109526bc12504831f8011 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Hi Kevin,
=A0
Thank you for the update,
=A0
>>What does the table look like? =A0What indexes are there?
<= /div>
Table has a combination of byteas. Indexes are b-tree and Partial
=A0
>>Why are you doing that?
Our table face lot of updates and deletes in a day, so we prefer reind= ex to update the indexes as well overcome with a corrupted index.
=A0
>> How long?
More than 4 hrs..
=A0
>>What run time are you expecting?
Less than what it is taking at present.
=A0
>>It's hard to answer that without more information, like Po= stgreSQL
>>version and configuration, for starters. =A0See:
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 version
------------------------------------------------------------= ------------------------------------------------
=A0PostgreSQL 8.4.2 on = i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-= 44), 32-bit
(1 row)
Expected the performance question..
=A0
Regards
Raghavendra

On Thu, Apr 1, 2010 at 2:32 AM, Kevin Grittner <= span dir=3D"ltr"><Kevin.G= rittner@wicourts.gov> wrote:
raghavendra t <raagavendra.rao@gmail.com> wrote:

> I have a table w= ith 40GB size, it has few indexes on it.

What does the table l= ook like? =A0What indexes are there?

> When i try to REINDEX on the table,

Why are you doing that?

> its take a long time.

How long?

> I tried increasing the maintenance_work_mem, but= still i havnt
> find a satisfying result.

What run time= are you expecting?

> Questions
> =3D=3D=3D=3D=3D=3D=3D
> = 1. What are the parameters will effect, when issuing the REINDEX
> = =A0 =A0command
> 2. Best possible way to increase the spead of the RE= INDEX

It's hard to answer that without more information, like PostgreSQL
v= ersion and configuration, for starters. =A0See:

http://wiki.po= stgresql.org/wiki/SlowQueryQuestions

My best guess is that you can make them instantaneous by not runningthem. =A0A good VACUUM policy should make such runs unnecessary in
most= cases -- at least on recent PostgreSQL versions.

-Kevin

--000e0cd109526bc12504831f8011--