Re: Estimate maintenance_work_mem for CREATE INDEX

On 19 December 2017 at 10:00, Oleksandr Shulgin
<oleksandr(dot)shulgin(at)zalando(dot)de> wrote:

> If there would be an option in the database itself to provide those
> estimation, we wouldn't even need to figure out estimation queries.
> "EXPLAIN CREATE INDEX" anyone?

You're not the first to propose something like that. I think an
EXPLAIN ALTER TABLE would also be very handy -- it's currently
impossible to tell without carefully reading the source code whether a
given DDL change will require a full table scan, a full table rewrite,
or just a quick meta data update (and even in that case what strength
lock will be required). I think there are other utility statements
that make interesting heuristic decisions that would be nice to be
able to have some visibility into -- CLUSTER comes to mind.

I'm not clear how you would determine how much memory is needed to
sort a table without actually doing the sort though. So that would be
more of an EXPLAIN ANALYZE wouldn't it?

--
greg