From: | Tory M Blue <tmblue(at)gmail(dot)com> |
---|---|
To: | PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: index build faster on 8G laptop than 30G server |
Date: | 2016-04-19 21:36:31 |
Message-ID: | CAEaSS0Zy5qqXwcUhhpuEBeJ5Pi8C5zZBCy3MmG+QqxqNQnOo3Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Apr 19, 2016 at 2:30 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:
> On 4/19/2016 2:28 PM, Bill Ross wrote:
>>
>> I've been running an index build for almost an hour on my 30G server that
>> takes ~ 20 mins on my puny old macbook.
>>
>> It seems like I've tuned all I can.. what am I missing?
>
>
>
>
> maintenance_work_mem is the main thing that affects index building times, I
> didn't see that in your list of tuning parameters. I generally set it to
> 1GB.
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
I've got some indexes that take well over 7 hours to create on a
256GB machine. Last time I posted for help, I was told they have yet
to really fix the tuple issue and that using a tiny number in work_mem
helped allieviate some of the issues. Still takes a long time but i'm
at 512MB, no slower than 7GB+
Here is what was told to me before
"Yes. There is a hard limit on the number of tuples than can be sorted
in memory prior to PostgreSQL 9.4. It's also the case that very large
work_mem or maintenance_work_mem settings are unlikely to help unless
they result in a fully internal sort.
There is evidence that the heap that tuple sorting uses benefits from
*lower* settings. Sometimes as low as 64MB.
We're working to make this better in 9.6."
Tory
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Devoy | 2016-04-19 21:37:00 | Re: index build faster on 8G laptop than 30G server |
Previous Message | Scott Mead | 2016-04-19 21:36:29 | Re: index build faster on 8G laptop than 30G server |