I have a large database import (100GB) on a Postgres table without indexes.
After the import, I want to create an index for queries as fast as possible. No data is accessed in the table as long as the index is not ready.
What is the quickest way of building the index? I have to build the index on 3 columns (two
date). The index creation takes about 2 hours, which is really not useful.
Is there any way I could speed up the index creation? Probably it's not better to set the index before data import, as this will slow down the import?
I am aware that Postgres has an option to create an index "without locking", but wouldn't that decrease performance, since it enables Postgres to access the data while index is being created?
This is a virtual machine. The memory of the server can be increased to my needs. Currently I have 32GB.
maintenance_work_mem are still at the default configuration values. I don't have SSD for the database. Would this certainly speed things up?
I don't have any
boolean flags or similar that could be used for partial indexes. In fact, I want each row in my table to be indexed.