Thread: Re: surprisingly slow creation of gist index used in exclude constraint

Chris Withers <chris@withers.org> writes:
>       It has 4.1 million rows in it and while importing the data only
>       takes a couple of minutes, when I did a test load into the new
>       cluster, building the mkt_profile_period_col1_col4_col2_chan_excl
>       index for the exclude constraint took 15 hours.

Don't recall for sure, but I think GIST index build is sensitive
to the maintenance_work_mem setting; did you have that cranked up?

>     - failing that, what can I do to import and then create the index
>       in the background?

CREATE INDEX CONCURRENTLY, perhaps.

            regards, tom lane

On 14/05/2020 21:31, Tom Lane wrote:
> Chris Withers <chris@withers.org> writes:
>>        It has 4.1 million rows in it and while importing the data only
>>        takes a couple of minutes, when I did a test load into the new
>>        cluster, building the mkt_profile_period_col1_col4_col2_chan_excl
>>        index for the exclude constraint took 15 hours.
> 
> Don't recall for sure, but I think GIST index build is sensitive
> to the maintenance_work_mem setting; did you have that cranked up?

postgres=# show maintenance_work_mem;
  maintenance_work_mem
----------------------
  2GB
(1 row)

Would it be worth turning that up more? The server has ~130GB memory.

>>      - failing that, what can I do to import and then create the index
>>        in the background?
> 
> CREATE INDEX CONCURRENTLY, perhaps.

How would I bring this into play with respect to the dump and load 
cycle? Is there an option to pg_dump or something else I should use or 
is this a case of awk'ing the create index in the dump?

Chris