Hi,

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?

cheers,

Chris

Hi,

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.

I feel like asking what I'm doing wrong here? The new server is pretty decent hardware...

Concrete questions:

- what, if anything, am I getting badly wrong here?

- what can I do to speed up creation of this index?

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

As you can imagine, a 15hr outage for an upgrade has not met with large amounts of happiness from the people whose application it is ;-)

Chris