Postgres: CPU bound CREATE INDEX

Question

I am running postgres server on a slowish older computer with a fast SSD disk, 2,4 GHz i5 processor, and 8 GBs of RAM. The computer is not a speed monster but I am surprised by the performance. Creating index in a table with 40,000,000 rows takes roughly half an hour. My settings are the following:

max_connections = 2
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 1GB
maintenance_work_mem = 512MB
min_wal_size = 1GB
max_wal_size = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100

When I looked at top output it looks like I am CPU bound (100% CPU) although the memory used by the database was around 500 MB and I would expect it to use more.

I am going to create the index only once per table during first data import. Are there any settings I could tweak to speed up this operation?

If you're CPU bound... there isn't much more that you can tweak. — joanolo
– joanolo, Commented Feb 27, 2017 at 23:19
Is is a computationally expensive index? That would explain it. Maybe you can show the CREATE INDEX statement. Does it get faster if you increase maintenance_work_mem? — Laurenz Albe
– Laurenz Albe, Commented Feb 28, 2017 at 10:48
I'll check with maintenance_work_mem. The creation is simple, CREATE INDEX index_name on name (varcharField); — wczekalski
– wczekalski, Commented Feb 28, 2017 at 18:05
@LaurenzAlbe how much should I increase maintenance_work_mem? Also is the time "normal by any means"? First encounters with progress so it's hard for me to tell. — wczekalski
– wczekalski, Commented Feb 28, 2017 at 18:06
You could try with 1GB maintenance_work_mem and see if that makes it faster. It really seems slow - on our systems we get about ten times that speed. To see where the time is spent, you could profile with perf or OProfile if you are on Linux (requires debugging symbols). — Laurenz Albe
– Laurenz Albe, Commented Mar 1, 2017 at 8:51

wczekalski · Accepted Answer · 2017-03-01 15:47:30Z

2

After increasing the maintenance_work_mem to 1GB and initialising the database cluster with C locale as opposed to the one default on my system, it took only 2 minutes.

answered Mar 1, 2017 at 15:47

wczekalski

7455 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres: CPU bound CREATE INDEX

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related