Обсуждение: poor performance when recreating constraints on large tables

BEGIN;

ALTER TABLE ONLY t1 DROP CONSTRAINT fk_t1_t2_id;

ALTER TABLE ONLY t1 ADD CONSTRAINT fk_t1_t2_id FOREIGN KEY(id) REFERENCES t2(id) 

ON DELETE CASCADE 

DEFERRABLE INITIALLY DEFERRED;

COMMIT;

Mike Broers <mbroers@gmail.com> writes:
> I am in the process of implementing cascade on delete constraints
> retroactively on rather large tables so I can cleanly remove deprecated
> data.  The problem is recreating some foreign key constraints on tables of
> 55 million rows+ was taking much longer than the maintenance window I had,
> and now I am looking for tricks to speed up the process, hopefully there is
> something obvious i am overlooking.

maintenance_work_mem?

            regards, tom lane

On Mon, Jun 6, 2011 at 6:10 PM, Mike Broers <mbroers@gmail.com> wrote:
> Thanks for the suggestion, maintenance_work_mem is set to the default of
> 16MB on the host that was taking over an hour as well as on the host that
> was taking less than 10 minutes.  I tried setting it to 1GB on the faster
> test server and it reduced the time from around 6-7 minutes to about 3:30.
>  this is a good start, if there are any other suggestions please let me know
> - is there any query to check estimated time remaining on long running
> transactions?

Sadly, no.  I suspect that coming up with a good algorithm for that is
a suitable topic for a PhD thesis.  :-(

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Samuel Gendler <sgendler@ideasculptor.com> wrote:

> The planner knows how many rows are expected for each step of the
> query plan, so it would be theoretically possible to compute how
> far along it is in processing a query based on those estimates,
> wouldn't it?

And it is sometimes off by orders of magnitude.  How much remaining
time do you report when the number of rows actually processed so far
is five times the estimated rows that the step would process?  How
about after it chugs on from there to 20 time she estimated row
count?  Of course, on your next query it might finish after
processing only 5% of the estimated rows....

-Kevin

---------- Forwarded message ----------
From: Claudio Freire <klaussfreire@gmail.com>
Date: Wed, Jun 8, 2011 at 11:57 PM
Subject: Re: [PERFORM] poor performance when recreating constraints on
large tables
To: Samuel Gendler <sgendler@ideasculptor.com>


On Wed, Jun 8, 2011 at 9:57 PM, Samuel Gendler
<sgendler@ideasculptor.com> wrote:
> Sure, but if it is a query that is slow enough for a time estimate to be
> useful, odds are good that stats that are that far out of whack would
> actually be interesting to whoever is looking at the time estimate, so
> showing some kind of 'N/A' response once things have gotten out of whack
> wouldn't be unwarranted.  Not that I'm suggesting that any of this is a
> particularly useful exercise.  I'm just playing with the original thought
> experiment suggestion.

There's a trick to get exactly that:

Do an explain, fetch the expected rowcount on the result set, add a
dummy sequence and a dummy field to the resultset "nextval(...) as
progress".

Now, you won't get to read the progress column probably, but that
doesn't matter. Open up another transaction, and query it there.
Sequences are nontransactional.

All the smarts about figuring out the expected resultset's size
remains on the application, which is fine by me.

Samuel Gendler wrote:
> Sure, but if it is a query that is slow enough for a time estimate to
> be useful, odds are good that stats that are that far out of whack
> would actually be interesting to whoever is looking at the time
> estimate, so showing some kind of 'N/A' response once things have
> gotten out of whack wouldn't be unwarranted.

The next question is what are you then going to do with that information?

The ability to track some measure of "progress" relative to expectations
is mainly proposed as something helpful when a query has gone out of
control.  When that's happened, the progress meter normally turns out to
be fundamentally broken; the plan isn't happening at all as expected.
So, as you say, you will get an "N/A" response that says the query is
out of control, when in the cases where this sort of thing is expected
to be the most useful.

At that point, you have two choices.  You can let the query keep running
and see how long it really takes.  You have no idea how long that will
be, all you can do is wait and see because the estimation is trashed.
Or you can decide to kill it.  And the broken progress meter won't help
with that decision.  So why put it there at all?

What I try to do as a force of habit is run just about everything that
might take a while with "\timing" on, and try to keep statement_timeout
to a reasonable value at all times.  Do that enough, and you get a feel
for what reasonable and unreasonable time scales look like better than
the query executor can be expected to figure them out for you.  It would
be nice to provide a better UI here for tracking progress, but it would
really work only in the simplest of cases--which are of course the ones
you need it the least for.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books