It seems that gin creation is triggering something nasty in the
server that depends on previous history of the server.
If I vacuum full than drop the index and recreate it even with
maintenance_work_mem='200MB' index creation may take forever.
Stopping the execution may make vacuuming very slow or stopping the
server very slow etc...
I know that the server may be cleaning the new partially created
index etc... but gin creation even with 200MB of
maintenance_work_mem is having a too strange and to big hit on the
server.
I can *occasionally* succede to create a gin index in reasonable
time if I shut down the server, vacuum full and drop and recreate
the index separately from the transaction that load the data.
gist creation is *predictably* much faster even inside the
transaction that load the data and doesn't have side effect outside
index creation.
Even when gin creation succede it is definitively slower than 3x
gist/gin index creation looks more cpu bounded than memory bounded.
I'm checking if I made some mistake in other cfg parameters that may
have some impact on index creation...
Any further clue?
--
Ivan Sergio Borgonovo
http://www.webthatworks.it