Re: Thinking about EXPLAIN ALTER TABLE

On Fri, Dec 7, 2018 at 1:18 PM Greg Stark <stark(at)mit(dot)edu> wrote:
> I've been poking around with a feature I've wanted a number of times
> in the past, "EXPLAIN ALTER TABLE". The idea is that there are a bunch
> of optimizations in ALTER TABLE to minimize the amount of work and
> lock levels but it's really hard for users to tell whether they've
> written their ALTER TABLE commands carefully enough and properly to
> trigger the optimizations. As a result it's really easy for to
> accidentally take an exclusive lock and/or do a full table rewrite
> when you were expecting to just do a quick catalog update.

Seems like an important project.

> Are there are other aspects of alter table that people would like to
> see exposed that I haven't thought of?

How about:

* Whether CLUSTER does a sort, or a full index scan.

* How many workers will be used by CREATE INDEX, if any.

(These are both examples of utility statements that make very limited
use of the optimizer -- CREATE TABLE AS SELECT is covered by explain
already, and is closer to a DML statement.)

* Whether an external sort will be used by CREATE INDEX or CLUSTER,
plus other details predicted by cost_sort().

These additional items are just nice-to-haves that I'm throwing out as
suggestions.

> I'm thinking I should try to move all these decisions to phase 1 as
> much as possible but I'm not sure how feasible it will be to get the
> results exactly correct. Of course the cases where it's hardest to
> predict are precisely where users would most like to know what's going
> to happen...

I'm not sure either, but I suspect that some compromise will be
required. For example, it's not at all clear whether or not a lock
will be acquired on TOAST tables in many instances. Also, the planner
itself can acquire ASLs on indexes in order to think about using them,
without execution going on to make use of them, which can matter (e.g.
there could be a REINDEX of an index that the planner isn't going to
use, that nonetheless necessitates that the planner wait).

What you're talking about here isn't really "EXPLAIN, but for DDL" so
much as a way of making EXPLAIN predict relation lock acquisitions,
including for DDL statements. I'm fine with calling that EXPLAIN, but
it's still not quite EXPLAIN as we know it today.

--
Peter Geoghegan