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?
work_mem
option set to? It's not the same thing asmaintenance_work_mem
.. postgresql.org/docs/9.3/static/runtime-config-resource.htmlwork_mem
is default (1MB), butCREATE INDEX
doesn't depend on it.CREATE INDEX
doc page now as well as the link I pasted. Sorry for the misdirection.