Thread: progress of long running operation
Is there any way to get some insight into the progress of: insert into foo select distinct on (...) from bar where... It's got to with importing some legacy data, which has no proper primary key, and duplicates, and garbage that won't be accepted.And there's 30,000,000 rows, and I'm running on a slow disk for testing--so I know this is going to be painfullyslow. But after a few hours I'd like to know if it's going to finish overnight, or if it will take so long thatI need to look at alternate approaches. (I upped my shared buffers & work mem, so explain on the select statement shows a bit better than 50% reduction in predictedwork for that part. And I will go ahead and drop all indexes on the target table.) -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
Scott Ribe <scott_ribe@elevated-dev.com> writes: > Is there any way to get some insight into the progress of: > insert into foo select distinct on (...) from bar where... Watching the physical size of the foo table might be close enough. Or you could run contrib/pgstattuple's pgstattuple() function every so often --- it will report the uncommitted tuples as "dead", which is inaccurate, but you'd be able to see how fast the number is increasing. regards, tom lane
On Dec 27, 2012, at 12:46 PM, Tom Lane wrote: > Or you could run contrib/pgstattuple's pgstattuple() function every so > often --- it will report the uncommitted tuples as "dead", which is > inaccurate, but you'd be able to see how fast the number is increasing. That's exactly the kind of thing I was hoping for. I'm actually inserting into an empty table, so "dead" tuples would bedead accurate in my case ;-) Or I could suck it up and do them in batches instead of one giant pass... -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice