Re: How to fast the REINDEX - Mailing list pgsql-performance

From Hannu Krosing
Subject Re: How to fast the REINDEX
Msg-id 1270119273.6482.212.camel@hvost
Whole thread Raw
In response to Re: How to fast the REINDEX  (raghavendra t <>)
List pgsql-performance
On Thu, 2010-04-01 at 04:27 +0530, raghavendra t wrote:
>         I'm sorry I couldn't come up with more, but what you've
>         provided so
>         far is roughly equivalent to me telling you that it takes over
>         four
>         hours to travel to see my Uncle Jim, and then asking you how I
>         can
>         find out how he's doing in less time than that.  There's just
>         not
>         much to go on.  :-(
>         If you proceed with the course suggested in the URL I
>         referenced,
>         people on the list have a chance to be more helpful to you.
> Instead of looking into the priority of the question or where it has
> to be posted, it would be appreciated to keep a discussion to the
> point mentioned.  Truely this question belong to some other place as
> you have mentioned in the URL. But answer for Q1 might be expected
> alteast.

Ok, here is my answer to your Q1:

Q1. What are the parameters will effect, when issuing the REINDEX

A: Assuming you meant what parameters affect performance of REINDEX

Most parameters that affect general performance affect also REINDEX

Some that affect more are:

* amount of RAM in your server - the most important thing

* speed of disk subsystem - next most important in case not all of
active data fits in memory


*  maintenance_work_mem - affects how much of sorting can be done in
memory, if you can afford to have maintenance_work_mem > largest index
size then sorting for index creation can be done in RAM only and is
significantly faster than when doing tape sort with intermediate files
on disks.

* wal_buffers - the bigger the better here, but competes with how big
you can make maintenance_work_mem . If more of heap and created indexes
can be kept in shared memory, everything runs faster.

* checkpoint_segments - affects how often whole wal_buffers is synced to
disk, if done too often then wastes lot of disk bandwidth for no good

* other chekpoint_* - tune to avoid excessive checkpointing.

> Hope i could get the information from the other Thread in other
> catagory.

Nah, actually [PERFORM] is the right place to ask.

Just most people got the impression that you may be doing unnecessary
REINDEXing, and the best way to speed up unneeded things is not to do
them ;)

> Thank you
> Regards
> Raghavendra

Hannu Krosing
PostgreSQL Scalability and Availability
   Services, Consulting and Training

pgsql-performance by date:

From: Ľubomír Varga
Subject: Some question
From: Hannu Krosing
Subject: Re: Does the psql executable support a "fetch many" approach when dumping large queries to stdout?