CODEWITHSUNDEEP
postgresqlindexing
user3030851
user3030851

Reputation: 123

speed up GIN index creation

I'm trying to create a GIN index on an hstore column, avg 3KB per row and 2.5M rows. It takes ages (I gave up after 40 minutes). Gist comes back in couple of minutes. Any way to speed this up (or any idea how long it should take...)? I tried increasing maintenance_work_mem but if I push it over 1GB postgres refuses to start, complaining there are errors in postgresql.conf. Environment: windows 7 64bits, 8GB RAM, Intel Core i5 and an SSD drive. Any help/suggestion appreciated!

Update: tried change the parameter before the index creation. That's what I got...

ERROR: 2097152 is outside the valid range for parameter "maintenance_work_mem" (1024 .. 2097151) ****** Error ******

ERROR: 2097152 is outside the valid range for parameter "maintenance_work_mem" (1024 .. 2097151) SQL state: 22023

Upvotes: 1

Views: 1866

Answers (1)

jjanes
jjanes

Reputation: 44167

Increasing maintenance_work_mem is a key to good gin index creation speeds. I can certainly increase it to more than 1GB, so you must be doing something wrong, but without seeing the exact error message or the line from the conf file, it is hard to say what that might be.

But it is a bad practise to increase the setting in the config file just to create an index, you should instead increase it in the session which is doing the creation, using something like:

set maintenance_work_mem TO "5GB";

Upvotes: 2

Related Questions