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?