Re: Slow GIN indexes after bulk insert

Chris Spencer <chrisspen@gmail.com> writes:
> What constitutes a "large" work_mem? My server has 61GB of memory and my
> work_mem is currently set to include all of that.

Ouch.  That's a mistake independently of GIN.  The primary usage of
work_mem is to define how much memory an individual sorting or hashing
query step is allowed to use.  A complex query might have several sort or
hash steps, and then you need to worry about concurrent queries in
different sessions; not to mention that this is not the only demand on
your server's RAM.  I'd be hesitant to set work_mem much above 1GB, maybe
even quite a bit less than that depending on what your workload is like.

Cutting work_mem to ~100MB might alone be enough to fix your GIN issue;
if not you could experiment with forced flushes of the GIN pending lists
via VACUUM (or ANALYZE might do it too, and be more directly useful).

            regards, tom lane