Estimate maintenance_work_mem for CREATE INDEX

On Tue, Dec 19, 2017 at 10:47 AM, Oleksandr Shulgin <
oleksandr.shulgin@zalando.de> wrote:

(cross-posting admin and hackers)

Hello,

I wonder if I'm alone in my wish to have a way for estimating how much
maintenance work memory would suffice to allocate for a session when
creating an index and avoid spilling to disk?

Recently I had to re-create some indexes on a 9.6 server and I had some
input on the on-disk index size: one was around 30 GB, the other -- a bit
over 60 GB according to \di+ output. The total number of live tuples in
the table itself was close to 1.3e+9, the table had an estimated 25% bloat.

I had some spare memory on the machine so I've given it 60 GB for
maintenance_work_mem and expected that at least the smaller of the two will
fit in memory completely. To my surprise that didn't suffice and both
indexes were building with some disk spill.

Is anyone aware of a query to estimate the memory requirements for CREATE
INDEX [CONCURRENTLY]?

I've looked in the postgres wiki, but didn't find anything to that end.
Nor searching the archives of pgsql-admin did help.

I understand that there were some changes in recent releases related to
memory allocation (e.g. allowing huge allocation in 9.4), but at least
targeting 9.6 or 10 would make sense. There are also a lot of ways how one
CREATE INDEX can be different from the other, but in the most simple case
where you have fixed-width columns and building the full index (i.e. no
WHERE clause), it should be possible.

Now I see I fail to mention this is the default btree index with all
default options. Obviously other indexes can be very different in memory
requirements.

Not hasting to look in the source to calculate all the sizeof()s yet:

waiting on your reply and suggestions. ;-)

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?

Regards,
--
Oleksandr "Alex" Shulgin | Database Engineer | Zalando SE | Tel: +49 176
127-59-707

On 19 December 2017 at 10:00, Oleksandr Shulgin
<oleksandr.shulgin@zalando.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

On Dec 19, 2017, at 7:14 AM, Greg Stark <stark@mit.edu> wrote:

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?

I'd think EXPLAIN could use a quite rough approximation, say based on a rather sparse random sampling of values. EXPLAIN ANALYZE might involve a whole table scan, or might involve more dense sampling.

--
Scott Ribe
https://www.linkedin.com/in/scottribe/
(303) 722-0567

On Tue, Dec 19, 2017 at 3:15 PM Greg Stark <stark@mit.edu> wrote:

On 19 December 2017 at 10:00, Oleksandr Shulgin
<oleksandr.shulgin@zalando.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.

Yes, that would be pretty handy.

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?

My idea would be to use statistic. So that EXPLAIN CREATE INDEX (or
whatever the actual interface could be like) would benefit from up-to-date
statistic produced by ANALYZE.

Based on the estimated number of rows in the table, average width of
column(s) to index and taking into account the bookkeeping structures one
should be able to arrive at a good guess for the amount of memory the
backend would end up allocating (assuming it is available).

Having done that, as the first step, and using statistic again we could
also infer (though, probably with less accuracy) memory requirements for
building partial indexes. Functional indexes would be harder to tackle, I
would think this is only possible if the return type(s) of the function(s)
has all fixed width.

I didn't look in the code, but I imagine the procedure to read -> sort
-> spill to tapes, if needed -> merge sort the tapes is generic to all
index types, so this shouldn't be a breaking change for any user-defined
indexes (is this already a thing?). OK, maybe it's only generic for B-Tree
and BRIN, but not for GIN and GiST, to name a few. Damn, I gotta look in
the code at some point. ;-)

To let me fantasize a little more, what I would also love to see is the
estimated on-disk size for the resulting index, before starting to create
it. This is obviously dependent on the actual index type and options, such
as fill-factor, etc.

Cheers,
--
Alex

On Tue, Dec 19, 2017 at 11:14 PM, Greg Stark <stark@mit.edu> wrote:

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.

An application of such things is attempting to estimate the amount of
disk space needed when doing a schema upgrade, so that could be handy.
--
Michael