From: Greg Smith <greg@2ndquadrant.com>
To: Samuel Gendler <sgendler@ideasculptor.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: poor performance when recreating constraints on large tables
Date: Thu, 09 Jun 2011 01:57:25 -0400
Message-ID: <4DF060C5.8000702@2ndquadrant.com> (raw)
In-Reply-To: <BANLkTim5GO2k0m7E2=KevdZrMPwH-9aCDg@mail.gmail.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndquadrant.com, sgendler@ideasculptor.com, Kevin.Grittner@wicourts.gov
Subject: Re: poor performance when recreating constraints on large tables
In-Reply-To: <4DF060C5.8000702@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox