Message-ID: <4DF060C5.8000702@2ndquadrant.com>
Date: Thu, 09 Jun 2011 01:57:25 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Samuel Gendler <sgendler@ideasculptor.com>
CC: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	pgsql-performance@postgresql.org
Subject: Re: poor performance when recreating constraints on large
 tables
References: <BANLkTimAhAjMY3qZjxOFENugM+dcy6LmEA@mail.gmail.com>
	<12692.1307392671@sss.pgh.pa.us>
	<BANLkTinBY9j=v_0Oc5-rVuKcaEuj9f4tYQ@mail.gmail.com>
	<BANLkTi=zFRZb5AKXakVxvZqO=QWWCEGKZw@mail.gmail.com>
	<BANLkTikpLO0Tz07LGK2G5cVXrG0DA1xCMA@mail.gmail.com>
	<4DEF8CEC020000250003E38D@gw.wicourts.gov>
	<BANLkTim5GO2k0m7E2=KevdZrMPwH-9aCDg@mail.gmail.com>
In-Reply-To: <BANLkTim5GO2k0m7E2=KevdZrMPwH-9aCDg@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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