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?

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.