Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 41AC09FB367
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed,  7 Mar 2007 15:02:37 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024)
	with ESMTP id 75905-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed,  7 Mar 2007 15:02:31 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from sn.sai.msu.ru (sn.sai.msu.ru [195.208.220.215])
	by postgresql.org (Postfix) with ESMTP id 6DCB99FB272
	for <pgsql-sql@postgresql.org>; Wed,  7 Mar 2007 15:02:30 -0400 (AST)
Received: from sn.sai.msu.ru (localhost [127.0.0.1])
	by sn.sai.msu.ru (8.12.8/8.12.8) with ESMTP id l27J2PqR015269;
	Wed, 7 Mar 2007 22:02:25 +0300
Received: from localhost (megera@localhost)
	by sn.sai.msu.ru (8.12.8/8.12.8/Submit) with ESMTP id l27J2PmO015266;
	Wed, 7 Mar 2007 22:02:25 +0300
X-Authentication-Warning: sn.sai.msu.ru: megera owned process doing -bs
Date: Wed, 7 Mar 2007 22:02:25 +0300 (MSK)
From: Oleg Bartunov <oleg@sai.msu.su>
X-X-Sender: megera@sn.sai.msu.ru
To: Sumeet <asumeet@gmail.com>
cc: pgsql-sql@postgresql.org
Subject: Re: tesearch2 question
In-Reply-To: <7539aebb0703070746y5d3b779dlf9d37eea3caf5de@mail.gmail.com>
Message-ID: <Pine.LNX.4.64.0703072157410.400@sn.sai.msu.ru>
References: <7539aebb0703070746y5d3b779dlf9d37eea3caf5de@mail.gmail.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.189 tagged_above=0 required=5 tests=AWL, BAYES_50,
	DNS_FROM_RFC_ABUSE
X-Spam-Level: 
X-Archive-Number: 200703/73
X-Sequence-Number: 27711

On Wed, 7 Mar 2007, Sumeet wrote:

> Hi All,
>
> I'm trying to udpate a table containing  13149741 records. And its taking
> forever to complete this process.
>
> The update query i'm trying to run is for full text indexing similiar to
>
> UPDATE tblMessages SET idxFTI=to_tsvector(strMessage);
>

How big are your strMessage ? and what's your tsearch2 configuration ?
Can you estimate how long takes updating, for example, 1000 rows ?
It looks like your system is IO bound. What's your hardware ?

>
> Below are some of the stats which might be helpful for analyzing this
>
> $top
>
>  PID USERNAME LWP PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
> 3091 postgres   1  43    0   46M   38M cpu/1  200:06  3.20% postgres
> 5052 postgres   1  60    0  149M  134M sleep    0:17  3.12% postgres
>
> <<<here are the top 2 processes, out of which the first process i have been
> running almost for a day and a half and it is still running,
>
> This table which i'm trying to  update has 10 indexes
>
> =========================================================
> "a_article_pk" PRIMARY KEY, btree (id)
>   "a_article_uk_pmid" UNIQUE, btree (pmid)
>   "a_article_idx_abstract" btree ("substring"(abstract::text, 0, 255))
>   "a_article_idx_date_cr_year" btree (date_cr_year)
>   "a_article_idx_ml_journal_info_medline_ta" btree (ml_journal_info_a_ta)
>   "a_article_idx_owner" btree ("owner")
>   "a_article_idx_pmid" btree (pmid)
>   "a_article_idx_status" btree (status)
>   "a_article_idx_title" btree (article_title)
>   "a_master_t_idx_year_published" btree (published_year)
> ========================================================
> But no indexes on the field i'm trying to update. The field i'm trying to
> add is a new field.
> Can anyone help me out to figure out why is it taking so much time to update
> the table.
>
> Also as u see in the above indexes, I have some indexes on some varchar
> column which i feel are totally useless unless u so a exact string match.
> But does that help in any sense for improving the speed of retreiving the
> string just normally without any search on it?
>
> Thanks,
> Sumeet.
>

 	Regards,
 		Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83