From: Sumeet <asumeet@gmail.com>
To: pgsql-sql@postgresql.org
Subject: tesearch2 question
Date: Wed, 7 Mar 2007 10:46:07 -0500
Message-ID: <7539aebb0703070746y5d3b779dlf9d37eea3caf5de@mail.gmail.com> (raw)
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
<<<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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: asumeet@gmail.com
Subject: Re: tesearch2 question
In-Reply-To: <7539aebb0703070746y5d3b779dlf9d37eea3caf5de@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox