Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 2EA6DB5DE18 for ; Wed, 8 Jun 2011 16:57:57 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 73276-03 for ; Wed, 8 Jun 2011 19:57:50 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pz0-f46.google.com (mail-pz0-f46.google.com [209.85.210.46]) by mail.postgresql.org (Postfix) with ESMTP id B8FEAB5DBF4 for ; Wed, 8 Jun 2011 16:57:49 -0300 (ADT) Received: by pzk9 with SMTP id 9so405572pzk.19 for ; Wed, 08 Jun 2011 12:57:49 -0700 (PDT) MIME-Version: 1.0 Received: by 10.68.30.66 with SMTP id q2mr1046938pbh.390.1307563068946; Wed, 08 Jun 2011 12:57:48 -0700 (PDT) Received: by 10.68.66.195 with HTTP; Wed, 8 Jun 2011 12:57:48 -0700 (PDT) X-Originating-IP: [66.215.95.51] In-Reply-To: <4DEF8CEC020000250003E38D@gw.wicourts.gov> References: <12692.1307392671@sss.pgh.pa.us> <4DEF8CEC020000250003E38D@gw.wicourts.gov> Date: Wed, 8 Jun 2011 12:57:48 -0700 Message-ID: Subject: Re: poor performance when recreating constraints on large tables From: Samuel Gendler To: Kevin Grittner Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=bcaec51dd5d33ef74504a538c0b5 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201106/87 X-Sequence-Number: 44020 --bcaec51dd5d33ef74504a538c0b5 Content-Type: text/plain; charset=ISO-8859-1 On Wed, Jun 8, 2011 at 12:53 PM, Kevin Grittner wrote: > Samuel Gendler 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.... > 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. > > -Kevin > --bcaec51dd5d33ef74504a538c0b5 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Wed, Jun 8, 2011 at 12:53 PM, Kevin G= rittner <Kevin.Grittner@wicourts.gov> wrote:
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. =A0How 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? =A0How
about after it chugs on from there to 20 time she estimated row
count? =A0Of course, on your next query it might finish after
processing only 5% of the estimated rows....

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 a= ctually be interesting to whoever is looking at the time estimate, so showi= ng some kind of 'N/A' response once things have gotten out of whack= wouldn't be unwarranted. =A0Not that I'm suggesting that any of th= is is a particularly useful exercise. =A0I'm just playing with the orig= inal thought experiment suggestion.
=A0

-Kevin

--bcaec51dd5d33ef74504a538c0b5--