still gin index creation takes forever

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: still gin index creation takes forever
Date: 2008-11-11 23:49:00
Message-ID: 20081112004900.62346119@dawn.webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Smith 2008-11-12 01:18:33 Re: SHMMAX and shared_bufffers
Previous Message Dmitry Koterov 2008-11-11 22:31:24 Announce: PGUnit - xUnit test framework for pl/pgsql