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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
