Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id F09DE9FB4EC for ; Wed, 7 Mar 2007 16:40:44 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 62273-07 for ; Wed, 7 Mar 2007 16:40:27 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id C96CC9FB467 for ; Wed, 7 Mar 2007 16:40:37 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so646359ugf for ; Wed, 07 Mar 2007 12:40:36 -0800 (PST) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=HuK5/LiVEMKg8qIcK8+AZLRtieG9P4vkBUWyAWivGaNp5xkrBj2KTwwrY2xyTbdNzCgPsD1yfr9+7w8thCvGNOrApPI85h6ysJ0btGIIsXsNllgCKo6lEl3K+idldY5NvzR8TQnR5a2f3zzEOE3KKoXdymbu0ytLJ/rrElAfJGA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=TZ/bDGBMXZ8WV587k0RNsnlC7JcbBamC04FnrOw0hqLfyB1hoZV67POyuD1e/v4zrEsH2aZtC+gSe2BknzV5T319/V7th8OB3KlWSGYXKzCI0sQyy9iIp5KkfVfc9SeZuHj+WNGOjsAu4pmHE9pXW/8WafHbepjiY1q8/cgzgQY= Received: by 10.114.195.19 with SMTP id s19mr2342691waf.1173300034925; Wed, 07 Mar 2007 12:40:34 -0800 (PST) Received: by 10.114.174.8 with HTTP; Wed, 7 Mar 2007 12:40:34 -0800 (PST) Message-ID: <7539aebb0703071240l6c9c091eq163b3df16040a06f@mail.gmail.com> Date: Wed, 7 Mar 2007 15:40:34 -0500 From: Sumeet To: "Oleg Bartunov" Subject: Re: tesearch2 question Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_209176_4751692.1173300034786" References: <7539aebb0703070746y5d3b779dlf9d37eea3caf5de@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.375 tagged_above=0 required=5 tests=BAYES_50, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200703/77 X-Sequence-Number: 27715 ------=_Part_209176_4751692.1173300034786 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanks Oleg, My String message are Abstracts of papers, I did a $ select avg(len) from (select length(abstract) as len from master_table limit 500) E; avg ----------------------- 1355.5907859078590786 (1 row) so length is approx 1400. I couldn't find any appropriate way to analyze the time for update queries, but what i did was a explain analyze $ explain analyze select to_tsvector(article_title) from master_table limit 1000; The total runtime was approx 500ms. The server is Sun OS 5.10, with around 8gigs of RAM and 6cpus. Thanks, Sumeet. On 3/7/07, Oleg Bartunov wrote: > > 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 > > > > << 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 > -- Thanks, Sumeet Ambre Master of Information Science Candidate, Indiana University. ------=_Part_209176_4751692.1173300034786 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanks Oleg,

My String message are Abstracts of papers, I did a

$ select avg(len) from (select length(abstract) as len from master_table limit 500) E;

          avg         
-----------------------
 1355.5907859078590786
(1 row)

so length is approx 1400.


I couldn't find any appropriate way to analyze the time for update queries, but what i did was a explain analyze

$ explain analyze select to_tsvector(article_title) from master_table limit 1000;

The total runtime was approx 500ms.

The server is Sun OS 5.10, with around 8gigs of RAM and 6cpus.

Thanks,
Sumeet.



On 3/7/07, Oleg Bartunov <oleg@sai.msu.su> wrote:
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



--
Thanks,
Sumeet Ambre
Master of Information Science Candidate,
Indiana University. ------=_Part_209176_4751692.1173300034786--