On 14/05/2020 21:31, Tom Lane wrote:
Chris Withers <chris@xxxxxxxxxxx> 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
