Re: poor performance when recreating constraints on large tables

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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books