very long update gin index troubles back? - Mailing list pgsql-general

From Ivan Sergio Borgonovo
Subject very long update gin index troubles back?
Date
Msg-id 20090123005543.69143f27@dawn.webthatworks.it
Whole thread Raw
Responses Re: very long update gin index troubles back?  (Teodor Sigaev <teodor@sigaev.ru>)
Re: very long update gin index troubles back?  (Oleg Bartunov <oleg@sai.msu.su>)
Re: very long update gin index troubles back?  (Teodor Sigaev <teodor@sigaev.ru>)
List pgsql-general
I've a table that contain a tsvector that is indexed (gin) and
triggers to update the tsvector that should then update the index.

This gin index has always been problematic. Recreation and updates
were very slow.

Now I had to update 1M rows of that table but for columns
that doesn't involve the tsvector
I dropped the trigger to update the tsvector so that when rows get
updated the trigger won't be called so things should be faster...
but still it is taking forever.

begin;
        set constraints all deferred;

        select * from FT1IDX_trigger_drop();
        update catalog_items set
        APrice=p.PrezzoA,
        BPrice=p.PrezzoB
        from import.catalog_prices p where
        catalog_items.ItemID=p.id;
    select * from FT1IDX_trigger_create();
commit;

function are used since I've 2 triggers actually that I drop and
create.

Is there anything wrong in the above to make this update so slow on
a 2x Xeon 3.2GHz 4GbRAM and a RAID1 [sic] I know it is slow on write.


--
Ivan Sergio Borgonovo
http://www.webthatworks.it


pgsql-general by date:

Previous
From: Vladimir Konrad
Date:
Subject: Re: problem converting database to UTF-8
Next
From: Clemens Schwaighofer
Date:
Subject: Re: how to avoid that a postgres session eats up all the memory