<p>My guess is your disk is the bottleneck.  Are you monitoring your CPU and disk for what's going on?  Using SSDs?  Other activity on the DB server?  Is the postgres server hitting SWAP a lot?  I can infer you have 30GB RAM to throw at it, but that's about all.</p>
<p>As mentioned already, clustering of indexes shuffles data on disk around.  The use of <code>--hstore-add-index</code> might compound that effect, those indexes will need to be updated as the underlying data is reordered unless I'm misunderstanding the order of operation.  Depending on the volume of tags in the data, those hstore indexes are more expensive compared to typical index overhead.</p>
<p>If your postgres server is not using replication you should set <code>wal_level=minimal</code> to reduce WAL activity and free up IO.  That config value defaults to <code>replica</code> in Pg10 and newer, in 9.6 and prior it defaulted to <code>minimal</code>.</p>
<p>The default <code>checkpoint_timeout=5min</code> and <code>checkpoint_completion_target=0.5</code> can probably benefit from adjusting too, those defaults are pretty bad.  By using <code>checkpoint_completion_target=0.9</code> and <code>checkpoint_timeout=30min</code> you can smooth out the related IO over a longer period of time reducing spikes.<br>
When I run osm2pgsql I set <code>checkpoint_timeout=1d</code> (the max), <a href="https://www.postgresql.org/docs/11/runtime-config-wal.html" rel="nofollow">read the docs and understand the risks</a> before doing that.  I never intend to run those</p>
<p>This all assumes you are stuck waiting for I/O...</p>

<p style="font-size:small;-webkit-text-size-adjust:none;color:#666;">—<br />You are receiving this because you commented.<br />Reply to this email directly, <a href="https://github.com/openstreetmap/osm2pgsql/issues/993?email_source=notifications&email_token=AA6353WO3AFNVNALEDAQKZ3QU2X55A5CNFSM4JP6WBJ2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEE2Y5WQ#issuecomment-557158106">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/AA6353XKFNRZS4O4GUUOYOLQU2X55ANCNFSM4JP6WBJQ">unsubscribe</a>.<img src="https://github.com/notifications/beacon/AA6353XD3SOFEB5RBXMP6Y3QU2X55A5CNFSM4JP6WBJ2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEE2Y5WQ.gif" height="1" width="1" alt="" /></p>
<script type="application/ld+json">[
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"potentialAction": {
"@type": "ViewAction",
"target": "https://github.com/openstreetmap/osm2pgsql/issues/993?email_source=notifications\u0026email_token=AA6353WO3AFNVNALEDAQKZ3QU2X55A5CNFSM4JP6WBJ2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEE2Y5WQ#issuecomment-557158106",
"url": "https://github.com/openstreetmap/osm2pgsql/issues/993?email_source=notifications\u0026email_token=AA6353WO3AFNVNALEDAQKZ3QU2X55A5CNFSM4JP6WBJ2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEE2Y5WQ#issuecomment-557158106",
"name": "View Issue"
},
"description": "View this Issue on GitHub",
"publisher": {
"@type": "Organization",
"name": "GitHub",
"url": "https://github.com"
}
}
]</script>