5

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 varchar, one 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. work_mem and 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.

migrated from stackoverflow.com Apr 28 '15 at 23:05

This question came from our site for professional and enthusiast programmers.

  • postgres has an option to create an index "without locking the table" if that's any help...in terms of raw speed importing all the data then creating the index "after" should be about fast as it can go, I'd wager... – rogerdpack Apr 28 '15 at 21:48
  • 2
    A SSD would most certainly speed this up. Increasing maintenance_work_mem might help as well. You could try something like set maintenance_work_mem = '1GB' (or even more) before running the CREATE INDEX if that is the only thing going on at that moment. – a_horse_with_no_name Apr 29 '15 at 7:54
4

If it's an option, you could pre-sort the data externally, before database import.

In my test using PostgreSQL 9.6.1, a table with three double precision columns and 10M records with random values, creating an index on the same data, but preordered, shaved off 70% of index creation time:

db=# create table indexing_test_data (a varchar(50), b varchar(50), c real);       
CREATE TABLE
Time: 3,586 ms
db=# insert into indexing_test_data select random()::text, random()::text, random() from generate_series(1, 10000000);
INSERT 0 10000000
Time: 25590,475 ms
db=# select a, b, c into indexing_test_sorted from indexing_test_data order by a, b, c;
SELECT 10000000
Time: 77389,665 ms

db=# create index test_data_idx on indexing_test_data (a, b, c);
CREATE INDEX
Time: 57399,140 ms

db=# create index test_sorted_idx on indexing_test_sorted (a, b, c);
CREATE INDEX
Time: 16219,639 ms

An extra speedup can be had if you can afford to use a database with C locale and collation, which can then use a feature called abbreviated keys, available since PostgreSQL 9.5. This speeds up sorts up to 20x, but only works on C locale, due to buggy locale support in older libraries:

db=# create database testdb lc_collate "C" lc_ctype "C" template template0;
CREATE DATABASE
Time: 429,710 ms
db=# \c testdb
You are now connected to database "testdb" as user "user".
testdb=# create table indexing_test_data (a varchar(50), b varchar(50), c real);       
CREATE TABLE
Time: 2,794 ms
testdb=# insert into indexing_test_data select random()::text, random()::text, random() from generate_series(1, 10000000);
INSERT 0 10000000
Time: 25977,964 ms
testdb=# select a, b, c into indexing_test_sorted from indexing_test_data order by a, b, c;
SELECT 10000000
Time: 20794,850 ms

testdb=# create index test_data_idx on indexing_test_data (a, b, c);
CREATE INDEX
Time: 16371,426 ms

testdb=# create index test_sorted_idx on indexing_test_sorted (a, b, c);
CREATE INDEX
Time: 8046,787 ms

Here, indexing sorted data takes around 50% time of unsorted, but compared to first version, that took 57 seconds, you're down to 14% of time.

Other things, as mentioned in the comments, can help too: setting maintenance_work_mem appropriately and not running create index concurrently (which is significantly slower).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.