Re: Slow GIN indexes after bulk insert

Chris Spencer <chrisspen(at)gmail(dot)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