On Mon, Nov 17, 2008 at 11:36 AM, Jeff Frost
<jeff@frostconsultingllc.com> wrote:
> 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?
I hate it when my brain is thinking maintenance_work_mem and my
fingers are typing work_mem.
You're right. It was maintenance_work_mem.