Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.
    Tweeted twitter.com/StackDBAs/status/816585646810824704
2 Incorporated comments; improved formatting; edited tags; edited title
source | link

How to create Create index on a postgresPostgres table as fast as possible?

I have a large database import (100GB) on a postgresPostgres table without indexesindexes. After

After the import, I want to create thean index for the queries as fast as possible. No data is accessed onin 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 varcharvarchar, one datedate). The index creation laststakes about 2h2 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.

How to create index on a postgres table as fast as possible?

I have a large database import (100GB) on a postgres table without indexes. After the import I want to create the index for the queries as fast as possible. No data is accessed on 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 lasts about 2h, 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?

Create index on a Postgres table as fast as possible

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.

    Post Migrated Here from stackoverflow.com
1
source | link

How to create index on a postgres table as fast as possible?

I have a large database import (100GB) on a postgres table without indexes. After the import I want to create the index for the queries as fast as possible. No data is accessed on 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 lasts about 2h, 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?