Adding new field to big table

От: acanada
Тема: Adding new field to big table
Дата: ,
Msg-id: 6CB45841-A1AF-493C-A223-20E335080867@cnio.es
(см: обсуждение, исходный текст)
Ответы: Re: Adding new field to big table  (Jeff Janes)
Список: pgsql-performance

Скрыть дерево обсуждения

Adding new field to big table  ("acanada", )
 Re: Adding new field to big table  (Jeff Janes, )
  Re: Adding new field to big table  ("acanada", )
   Re: Adding new field to big table  (Jeff Janes, )
    Re: Adding new field to big table  ("acanada", )
     Re: Adding new field to big table  (Jeff Janes, )
      Re: Adding new field to big table  (, )

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
updatesof 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
protegidapara el uso exclusivo de su destinatario. Se prohíbe la distribución, reproducción o cualquier otro tipo de
transmisiónpor parte de otra persona que no sea el destinatario. Si usted recibe por error este correo, se ruega
comunicarloal remitente y borrar el mensaje recibido. 
**CONFIDENTIALITY NOTICE** This email communication and any attachments may contain confidential and privileged
informationfor the sole use of the designated recipient named above. Distribution, reproduction or any other use of
thistransmission by any party other than the intended recipient is prohibited. If you are not the intended recipient
pleasecontact the sender and delete all copies. 




В списке pgsql-performance по дате сообщения:

От: "acanada"
Дата:
Сообщение: Re: Adding new field to big table
От: Jeff Janes
Дата:
Сообщение: Re: Adding new field to big table