PostgreSQL CREATE INDEX memory requirement

Question

I've got 6GB of RAM on my laptop and for a test I'm creating a 50mil-rows table in PostgreSQL 9.3. I then want to create an index on the table.

The table and the resulting index together (or twice the table total size) can fit into 5GB of RAM and I set maintenance_work_mem to 5GB, still CREATE INDEX uses external sort with about 1.4GB of temp files. Why is that so?

Is my expectation that it should be able to sort in RAM unreasonable?

test=# set maintenance_work_mem to '5GB';
SET
test=# create table t1 as (select i::int, random() as f from generate_series(1, 50000000) i);
SELECT 50000000
test=# select pg_size_pretty(pg_relation_size('t1'));
 pg_size_pretty 
----------------
 2111 MB
(1 row)
test=# create index on t1(f, i);
CREATE INDEX
test=# select pg_size_pretty(pg_relation_size('t1_f_i_idx'));
 pg_size_pretty 
----------------
 1504 MB
(1 row)

In the server log:

LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp22623.1", size 1073741824
STATEMENT: create index on t1(f, i);
LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp22623.2", size 327622656
LOG: external sort ended, 171065 disk blocks used: CPU 6.78s/268.73u sec elapsed 313.18 sec

Is there a way to calculate CREATE INDEX memory requirement before actually running it?

• Joishi Bodio: What is your work_mem option set to? It's not the same thing as maintenance_work_mem .. postgresql.org/docs/9.3/static/runtime-config-resource.html
• alex: My work_mem is default (1MB), but CREATE INDEX doesn't depend on it.
• Joishi Bodio: Ah - I see on the CREATE INDEX doc page now as well as the link I pasted. Sorry for the misdirection.
• alex: See my comment to the accepted answer. One other thing to consider is that using an --enable-cassert build (which I did) adds memory overhead on every palloc'd chunk (the sizeof(Size): usually 8, or 4 on x86 builds).

Answer 1

Through version 9.3, the indirection array used for sorting had to fit in a single 1GB memory allocation. This created an artificial limit on the number of tuples which could be sorted in memory. Once that limit was reached, it had to switch to a disk sort, even if there was memory left over.

This restriction was removed in version 9.4.

• Hans-Jürgen Schönig: keep in mind that more memory does not always make things faster in terms of sorting. we at cybertec have recently run a test indicating that an infinite amount of maintenance_work_mem does not necessarily speed things up - however, it depends a bit on the disk system as well.
• alex: Indeed, this was the root cause. There is 50x10^6 tuples to sort and the indirection array uses 24 bytes per tuple, hence it needs 1200x10^6 bytes. The relevant commit is this: github.com/postgres/postgres/commit/…