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

On Wed, 2021-12-22 at 09:58 +0000, Chris Withers wrote:
> A year and a half later, now that version 14 is the latest available,
> I wonder if anything has changed with respect to gist index creation?
> Probably also worth asking: are there now different index types this
> application should be using?
>
> > I'm upgrading a database from 9.4 to 11.5 by dumping from the old
> > cluster and loading into the new cluster.
> > The database is tiny: around 2.3G, but importing this table is proving problematic:
> >
> > Column | Type | Modifiers
> > ----------------+-------------------+----------------------------------------------------------
> >  period | tsrange | not null
> >  col1 | character varying | not null
> >  col2 | character varying | not null
> >  col3 | integer | not null
> >  col4 | character varying | not null default ''::character varying
> >  id | integer | not null default nextval('mkt_profile_id_seq'::regclass)
> >  deleted | boolean | not null default false
> >  managed | boolean | not null default false
> >  col5 | character varying |
> > Indexes:
> >     "mkt_profile_pkey" PRIMARY KEY, btree (id)
> >     "mkt_profile_period_col1_col4_col2_chan_excl" EXCLUDE USING gist (period WITH &&, col1 WITH =, col4 WITH =, col2 WITH =, col3 WITH =)
> > Check constraints:
> >     "mkt_profile_period_check" CHECK (period <> 'empty'::tsrange)
> > Foreign-key constraints:
> >     "mkt_profile_col1_fkey" FOREIGN KEY (col1) REFERENCES host(name)
> >
> > 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.

You could use "pg_upgrade" for upgrading, that will be much faster.

Creating GiST indexes is still slow. You could test if any of the improvements
since v11 have made your case faster.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com