Thread: How would I get information regarding update when running for a long time?

I have an update that is comparing the id between two tables and inserting the value volume in the first table with result from second table.

I think I have two questions.

1) is this update expected to take this long?
2) is there something that I did wrong with the update causing this slow time?

Is there a way of determining the progress for the update?

If the update is taking this long (now 1.5 hours) is there something wrong with the update statement?

It seems a long time to compare and update the 20 million rows, or maybe not?

Are the adds for the indexes for volume on the first table making the update slow?

Here is the update and table definitions for both tables

update t1111 set volume=tchris.volume from tchris where t1111.id=tchris.id;


pgdb001=# select count(*) from dbprc001.t1111unadjusted_prices;
  count  
----------
 19922778
(1 row)


pgdb001=# \d dbprc001.t1111unadjusted_prices
      Table "dbprc001.t1111unadjusted_prices"
    Column     |         Type          | Modifiers
---------------+-----------------------+-----------
id                | character varying(13) |
date            | date                     |
price            | numeric(18,6)         |
volume         | numeric(18,6)         |
Indexes:
    "ix_d111a" btree (id)
    "ix_d111b" btree (date)
    "ix_d111c" btree (price)
    "ix_d111d" btree (volume)



pgdb001=# \d dbprc001.tchris                
              Table "dbprc001.tchris"
    Column     |         Type          | Modifiers
---------------+-----------------------+-----------
id                | character varying(13) |
date            | date                  |
volume         | numeric(18,6)         |
Indexes:
    "ix_dchrisa" btree (id)
    "ix_dchrisb" btree (date)
    "ix_dchrisd" btree (volume)
 

Thanks for any help

Chris Barnes

---

More storage. Better anti-spam and antivirus protection. Hotmail makes it simple.

just use: explain update ...
and see what sort of plan it comes up with.

On Wed, Jul 22, 2009 at 10:00:38AM -0400, Chris Barnes wrote:
> 1) is this update expected to take this long?

1.5 hours is pretty slow, a couple of things spring to mind as to what
would make it go slow:

 1) it could be waiting for a lock (the pg_locks and pg_stat_activity
    views may help here)

 2) maintaining all those indexes is going to be slow (vmstat or better
    iostat will tell you what your system/disks are doing)

> 2) is there something that I did wrong with the update causing this
> slow time?

None of those indexes are enforcing a UNIQUE constraint, are you sure
that the id column in those tables uniquely identify the rows in the
table?  This isn't going to make it slow, but will cause you to get a
non-deterministic (i.e. normally "wrong") answer.

--
  Sam  http://samason.me.uk/