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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Chris Withers <chris(at)withers(dot)org>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: surprisingly slow creation of gist index used in exclude constraint
Date: 2020-05-14 20:31:10
Message-ID: 1424.1589488270@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Chris Withers <chris(at)withers(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Virendra Kumar 2020-05-14 20:45:06 Re: vacuumdb --jobs deadlock: how to avoid pg_catalog ?
Previous Message ktm 2020-05-14 20:16:53 Re: surprisingly slow creation of gist index used in exclude constraint