From: Antonin Houska <ah@cybertec.at>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: Robert Haas <robertmhaas@gmail.com>,
    Tatsuro Yamada <yamada.tatsuro@lab.ntt.co.jp>,
    "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] CLUSTER command progress monitor
In-reply-to: <16114.1511198753@sss.pgh.pa.us>
References: <59A77072.3090401@lab.ntt.co.jp>
 <CA+TgmobcMQEFvJkEqunpXfj2Uuc21Lt_0X_nJowd9CEebY15GQ@mail.gmail.com>
 <15653.1511197525@localhost> <16114.1511198753@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
   message dated "Mon, 20 Nov 2017 12:25:53 -0500."
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Date: Tue, 21 Nov 2017 10:14:11 +0100
Message-ID: <18222.1511255651@localhost>
Precedence: bulk

Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Antonin Houska <ah@cybertec.at> writes:
> > Robert Haas <robertmhaas@gmail.com> wrote:
> >> These two phases overlap, though. I believe progress reporting for
> >> sorts is really hard.
>=20
> > Whatever complexity is hidden in the sort, cost_sort() should have take=
n it
> > into consideration when called via plan_cluster_use_sort(). Thus I thin=
k that
> > once we have both startup and total cost, the current progress of the s=
ort
> > stage can be estimated from the current number of input and output
> > rows. Please remind me if my proposal appears to be too simplistic.
>=20
> Well, even if you assume that the planner's cost model omits nothing
> (which I wouldn't bet on), its result is only going to be as good as the
> planner's estimate of the number of rows to be sorted.  And, in cases
> where people actually care about progress monitoring, it's likely that
> the planner got that wrong, maybe horribly so.  I think it's a bad idea
> for progress monitoring to depend on the planner's estimates in any way
> whatsoever.

The general idea was that some sort of prediction of the total cost is need=
ed
anyway if we should tell during execution what fraction of work has already
been done. And also that the cost computation that we perform during execut=
ion
shouldn't (ideally) differ from cost_sort(). So I thought that it's easier =
to
refine cost_sort() than to implement the same computation from scratch
elsewhere.

Besides that I see 2 circumstances that make the estimate of the number of
input tuples simpler in the CLUSTER case:

* There's only 1 input relation w/o any kind of clause.

* CLUSTER uses SnapshotAny, so pg_class(reltuples) is closer to the actual
  number of input rows than it would be in general case. (Of course, pg_cla=
ss
  would only be useful for the initial estimate.)

Unlike planner, the executor could recalculate the cost estimate at some
point(s) as it recognizes that the actual number of tuples per page appears=
 to
differ from the density derived from pg_class initially. Still wrong?

--=20
Antonin Houska
Cybertec Sch=C3=B6nig & Sch=C3=B6nig GmbH
Gr=C3=B6hrm=C3=BChlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de, http://www.cybertec.at