surprisingly slow creation of gist index used in exclude constraint

Started by Chris Withersalmost 6 years ago8 messagesgeneral

chris@withers.org

almost 6 years ago

Kenneth Marshall

ktm@rice.edu

almost 6 years ago

In reply to: Chris Withers (#1)

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

Quoting Chris Withers <chris@withers.org>:

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
...
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

Hi Chris,

This sounds like a candidate for pg_logical replicating from the old
to new system.

Regards,
Ken

Tom Lane

tgl@sss.pgh.pa.us

almost 6 years ago

In reply to: Chris Withers (#1)

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

Chris Withers

chris@withers.org

almost 6 years ago

In reply to: Kenneth Marshall (#2)

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

On 14/05/2020 21:16, ktm@rice.edu wrote:

Hi Chris,

This sounds like a candidate for pg_logical replicating from the old to
new system.

Can you point me to a good guide as to how to easily set this up for one
database and would work between pg 9.4 and pg 11.5?

cheers,

Chris

Kenneth Marshall

ktm@rice.edu

almost 6 years ago

In reply to: Chris Withers (#4)

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

Hi Chris,

This sounds like a candidate for pg_logical replicating from the
old to new system.

Can you point me to a good guide as to how to easily set this up for
one database and would work between pg 9.4 and pg 11.5?

cheers,

Chris

Hi Chris,

Here is on for 9.4 to 10.3:

https://hunleyd.github.io/posts/Upgrading-PostgreSQL-from-9.4-to-10.3-with-pglogical/

and another article:

https://www.depesz.com/2016/11/08/major-version-upgrading-with-minimal-downtime/

It is a pretty straightforward procedure and really cuts the time needed
for an upgrade. Although, as Tom mentioned maybe just bumping your
maintenance_work_mem up to 1g or so may make the index create time
reasonable.

Regards,
Ken

Chris Withers

chris@withers.org

almost 6 years ago

In reply to: Tom Lane (#3)

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

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

Chris Withers

chris@withers.org

over 4 years ago

In reply to: Chris Withers (#1)

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

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
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 

<div class="moz-cite-prefix">On 14/05/2020 21:11, Chris Withers
wrote: 
</div>
<blockquote type="cite"
cite="mid:789b3464-9bfc-2157-f4f4-71eb1da1050d@withers.org">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
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:
<pre class="c-mrkdwn__pre" data-stringify-type="pre" style="box-sizing: inherit; margin: 4px 0px; padding: 8px; --saf-0:rgba(var(--sk_foreground_low,29,28,29),0.13); font-size: 12px; line-height: 1.50001; font-variant-ligatures: none; white-space: pre-wrap; overflow-wrap: break-word; word-break: normal; tab-size: 4; font-family: Monaco, Menlo, Consolas, "Courier New", monospace !important; border: 1px solid var(--saf-0); border-radius: 4px; background: rgba(var(--sk_foreground_min,29,28,29),0.04); counter-reset: list-0 0 list-1 0 list-2 0 list-3 0 list-4 0 list-5 0 list-6 0 list-7 0 list-8 0 list-9 0; color: rgb(29, 28, 29); font-style: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">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)</pre>
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 

</blockquote>
</body>
</html>

Laurenz Albe

laurenz.albe@cybertec.at

over 4 years ago

In reply to: Chris Withers (#7)

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