Postgres Pro
Downloads
Home   >   mailing lists

Re: Adding new field to big table - Mailing list pgsql-performance

From acanada
Subject Re: Adding new field to big table
Date
Msg-id E91DB097-26CA-4B7A-B814-94EF04C5C8A5@cnio.es
Whole thread Raw
In response to Re: Adding new field to big table  (Jeff Janes <jeff.janes@gmail.com>)
Responses Re: Adding new field to big table
List pgsql-performance
Tree view
Hello,

Jeff and Jeffrey thank you for your tips.
This is the explain of the query:
x=> explain update document as d set "svmConfidence" = st.svmconfidence from svm_confidence_id_tmp as st where st.id = d.id;
                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Update on document d  (cost=4204242.82..61669685.86 rows=124515592 width=284)
   ->  Hash Join  (cost=4204242.82..61669685.86 rows=124515592 width=284)
         Hash Cond: (d.id = st.id)
         ->  Seq Scan on document d  (cost=0.00..8579122.97 rows=203066697 width=270)
         ->  Hash  (cost=1918213.92..1918213.92 rows=124515592 width=18)
               ->  Seq Scan on svm_confidence_id_tmp st  (cost=0.00..1918213.92 rows=124515592 width=18)
(6 rows)

It's not using the index, most of the rows are beeing updated.
I'm trying with the CTAS solution.

Cheers,
Andrés.

El Mar 14, 2014, a las 8:29 PM, Jeff Janes escribió:

On Fri, Mar 14, 2014 at 10:06 AM, acanada <acanada@cnio.es> wrote:
Hello Jeff,

The lock time is not a problem. The problem is that takes too much time. I will need to add more fields to this table in the near future and I'd like to know if the process can be accelerated by any parameter, workaround or whatever...

Thank you for your answer.

OK.  Can you provide an explain (analyze, buffers), and the other information described here: http://wiki.postgresql.org/wiki/Slow_Query_Questions

It may be faster to make a new table by selecting a join on the existing tables and then replace the master table with it.

Also, if you are going to be doing a lot of bulk updates like this, lowering the fillfactor to below 50% might be helpful.
 
Cheers,

Jeff



**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.

pgsql-performance by date:

Previous
From: Jeff Janes
Date:
Subject: Re: Adding new field to big table
Next
From: Jeff Janes
Date:
Subject: Re: Adding new field to big table