Re: How to fast the REINDEX

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
command

A: Assuming you meant what parameters affect performance of REINDEX
command.

Most parameters that affect general performance affect also REINDEX
command.

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

Tunables

* 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
reason.

* 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 http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training