Hello,
I'm having time issues when adding new fields to a big table. I hope you can
point me some hints to speed up the updates of a table with 124 million rows...
This is what I do:
First I create a tmp_table with the data that will be added to the big table:
\d+ svm_confidence_id_tmp
Table "public.svm_confidence_id_tmp"
Column | Type | Modifiers | Storage | Stats target |
Description
---------------+------------------+-----------+---------+--------------+-------------
id | integer | not null | plain | |
svmconfidence | double precision | | plain | |
Indexes:
"svm_confidence_id_tmp_pkey" PRIMARY KEY, btree (id)
The table where this data will be added has the svmConfidence field
\d+ document;
Table "public.document"
Column | Type | Modifiers | Storage |
Stats target | Description
------------------+--------------------------------+-----------+----------+--------------+-------------
id | integer | not null | plain |
|
kind | character varying(255) | not null | extended |
|
uid | character varying(255) | not null | extended |
|
sentenceId | character varying(255) | not null | extended |
|
text | text | not null | extended |
|
hepval | double precision | | plain |
|
created | timestamp(0) without time zone | not null | plain |
|
updated | timestamp(0) without time zone | | plain |
|
cardval | double precision | | plain |
|
nephval | double precision | | plain |
|
phosval | double precision | | plain |
|
patternCount | double precision | | plain |
|
ruleScore | double precision | | plain |
|
hepTermNormScore | double precision | | plain |
|
hepTermVarScore | double precision | | plain |
|
svm | double precision | | plain |
|
svmConfidence | double precision | | plain |
|
Indexes:
"DocumentOLD_pkey" PRIMARY KEY, btree (id)
"document_cardval_index" btree (cardval)
"document_heptermnorm_index" btree ("hepTermNormScore" DESC NULLS LAST)
"document_heptermvar_index" btree ("hepTermVarScore" DESC NULLS LAST)
"document_hepval_index" btree (hepval DESC NULLS LAST)
"document_kind_index" btree (kind)
"document_nephval_index" btree (nephval DESC NULLS LAST)
"document_patterncount_index" btree ("patternCount" DESC NULLS LAST)
"document_phosval_index" btree (phosval DESC NULLS LAST)
"document_rulescore_index" btree ("ruleScore" DESC NULLS LAST)
"document_sentenceid_index" btree ("sentenceId")
"document_svm_index" btree (svm)
"document_uid_index" btree (uid)
Then I update the svmConfidence field of the document table like this:
update document as d set "svmConfidence" = st.svmconfidence from
svm_confidence_id_tmp as st where st.id = d.id;
But it takes too much time.
Is there something to take into account? Any hints?
Should I do it in a different way?
Thanks for your time...
Andrés
**NOTA DE CONFIDENCIALIDAD** Este correo electrónico, y en su caso los ficheros
adjuntos, pueden contener información protegida para el uso exclusivo de su
destinatario. Se prohíbe la distribución, reproducción o cualquier otro tipo de
transmisión por parte de otra persona que no sea el destinatario. Si usted
recibe por error este correo, se ruega comunicarlo al remitente y borrar el
mensaje recibido.
**CONFIDENTIALITY NOTICE** This email communication and any attachments may
contain confidential and privileged information for the sole use of the
designated recipient named above. Distribution, reproduction or any other use
of this transmission by any party other than the intended recipient is
prohibited. If you are not the intended recipient please contact the sender and
delete all copies.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
