Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 643859FB1C6 for ; Wed, 7 Mar 2007 11:53:31 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 02332-10 for ; Wed, 7 Mar 2007 11:53:25 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.233]) by postgresql.org (Postfix) with ESMTP id 22BE99FB687 for ; Wed, 7 Mar 2007 11:53:23 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id 71so304283wri for ; Wed, 07 Mar 2007 07:53:22 -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:mime-version:content-type; b=U+upbVWY4njj0nEslwh6Opcd36gTUo1DOR2DKJXKOVT54+tyU4qjS8VJgA/ASFBq121rfg1blaGx6vHpuAKE35bDj2hz8GoKgn60acIx/M0J34ZVmZLk/amIkwj/gvIdpF3vcswNgqlWnfBeAMWFdAC5yNLR7+qMcJLGEQfLBaw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=LzarBYMtLFOdhPe138aQhhhTwQGG7HL7hLkJRwTV6gAwGlj6W75ovNczUJLmAly/aq8Sj8xa2qRc3hERVXd8zg5ehAWEhwQxYJLahJjk2PdZw207WZpBKkFgQKQgBs365eBgvS17y0ADK9+6JAhINRVNWvXGmnQGaiAOb58W/bg= Received: by 10.114.153.18 with SMTP id a18mr2227710wae.1173282368028; Wed, 07 Mar 2007 07:46:08 -0800 (PST) Received: by 10.114.174.8 with HTTP; Wed, 7 Mar 2007 07:46:07 -0800 (PST) Message-ID: <7539aebb0703070746y5d3b779dlf9d37eea3caf5de@mail.gmail.com> Date: Wed, 7 Mar 2007 10:46:07 -0500 From: Sumeet To: pgsql-sql@postgresql.org Subject: tesearch2 question MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_201716_9882879.1173282367984" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200703/71 X-Sequence-Number: 27709 ------=_Part_201716_9882879.1173282367984 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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); 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 <<
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);

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.
------=_Part_201716_9882879.1173282367984--