I'm still fighting with my very long gin index creation that happens
randomly.
At the beginning I had a pretty long transaction that filled several
tables starting from some "temporary"[1] tables.
After filling the tables I updated a tsvector column in one of them
and finally in another connection I create a gin index on that
column.
I thought that I was encountering problems every time the index
creation was made in the same connection of the previous
transaction...
Actually it happened just by chance.
Now no matter if I open another connection to recreate the index...
it may still happen that it takes forever to recreate it.
maintenance_work_men is set to 200MB dynamically just before the
index get created.
Occasionally I can recreate the index in ~6min, especially if I did
some black magic before (eg. vacuum full, turn off DB) but very long
index creation happens mysteriously even when there is no process
stealing CPU cycles.
Anyway when I'm creating a gin index CPU use is very high staying
constantly near 100%.
Any suggestion about how to track down the problem?
thanks
[1] temporary but not strictly temp tables
--
Ivan Sergio Borgonovo
http://www.webthatworks.it