Re: Fwd: Performance Tuning

On Mon, 17 Nov 2008, Scott Marlowe wrote:

> On Mon, Nov 17, 2008 at 11:21 AM, John Zhang <johnzhang06@gmail.com> wrote:
>>
>>
>> Hi the list,
>>
>> I have a performance problem and would like to any input on how to make it
>> perform as desired. In the DB, there are a few tables over 3 million records
>> in postgis. When I do some operation on the tables, e.g. CREATE INDEX, it
>> takes hours without results. I believe there must be something wrong within
>> the DB. However, I have not figured it out. Could anyone point me to the
>> right direction to tune and configured the database efficiently?
>
> Assuming it's creating GIN indexes, and possibly even if not, index
> creation is very dependent on having a large enough work_mem for it to
> fit a large portion of the data it's working on in memory.  Try
> cranking up work_mem before index creation.  Note that you don't have
> to do this in postgresql.conf and reload, you can just do it in the
> session you're in:
>
> set work_mem=512000;
> create index blah blah blah;

Doesn't he want to change maintenance_work_mem instead of work_mem for index
creation?

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 916-647-6411    FAX: 916-405-4032