Thread: Percent of update completed

Is there a way to see how many rows of a table have been processed by an
update?  I've got an update on a 13,000,000 row table that's been
running for five days now.  Five days is a good long time, especially
considering the size of the table, so I'm suspecting a problem.  CPU is
pegged at 100%, so I'm relatively sure it's not an I/O bound issue.  But
I'd hate to kill it if it's, say, 90% of the way through.  No way I can
see to tell, though.

--Colin McGuigan

Colin McGuigan <cmcguigan@earthcomber.com> writes:
> Is there a way to see how many rows of a table have been processed by an
> update?  I've got an update on a 13,000,000 row table that's been
> running for five days now.

Not directly, but if you use the contrib/pgstattuple module you can get
readings on the numbers of committed and uncommitted tuples in the
table.  The rate at which the uncommitted-tuples count increases would
tell you how fast the update is proceeding.  (You should probably not
assume that you started with zero uncommitted tuples, unless you know
you'd vacuumed the table just beforehand.)

            regards, tom lane

Tom Lane wrote:
> Not directly, but if you use the contrib/pgstattuple module you can get
> readings on the numbers of committed and uncommitted tuples in the
> table.  The rate at which the uncommitted-tuples count increases would
> tell you how fast the update is proceeding.  (You should probably not
> assume that you started with zero uncommitted tuples, unless you know
> you'd vacuumed the table just beforehand.)

Thanks, Tom.

I've run it, and I see the dead_tuple_count, but I don't see an
uncommitted count.  The dead_tuple_count seems to oscillate, too -- it
increases slowly, but sometimes decreases immediately after.  Is that
normal?

I don't see an uncommitted tuples count, though.

--Colin McGuigan