Postgres multi-column index is taking forever to complete

Question

I have a table with around 270,000,000 rows and this is how I created it.

CREATE TABLE init_package_details AS
    SELECT pcont.package_content_id as package_content_id,
                    pcont.activity_id as activity_id,
                    pc.org_id as org_id,
                    pc.bed_type as bed_type,
                    pc.is_override as is_override,
                    pmmap.package_id as package_id,
                    pcont.activity_qty as activity_qty,
                    pcont.charge_head as  charge_head,
                    pcont.activity_charge as charge,
                    COALESCE(pc.charge,0) - COALESCE(pc.discount,0) as package_charge 
    FROM a pc
                    JOIN b od ON
                                (od.org_id = pc.org_id AND od.status='A')
                    JOIN c pm ON 
                                (pc.package_id=pm.package_id)
                    JOIN d pmmap ON
                                (pmmap.pack_master_id=pm.package_id)
                    JOIN e pcont ON 
                                (pcont.package_id=pmmap.package_id);

I need to build index on the init_package_details table.

This table is getting created at around 5-6 mins.

I have created btree index like,

CREATE INDEX init_package_details_package_content_id_idx 
   ON init_package_details(package_content_id);`

which is taking 10 mins (More than the time to create and populate the table itself)

And, when I create another index like,

CREATE INDEX init_package_details_package_act_org_bt_id_idx 
   ON init_package_details(activity_id,org_id,bed_type);

It just freezes and taking forever to complete. I waited for around 30 mins before I manually cancelled it.

Below are stats from iotop -o if it helps,

• When I created table Averaging around 110-120 MB/s (This is how 270 million rows got inserted in 5-6 mins)
• When I created First Index, It was averaging at around 70 MB/s
• On second index, it is snailing at 5-7 MB/s

Could someone explain Why is this happening? Is there anyway I can speedup the index creations here?

EDIT 1: There are no other connections accessing the table. And, pg_stat_activity shows active as status throughout the running time. This happens inside a transaction (this is happening between BEGIN and COMMIT, it contains many other scripts in same .sql file).

EDIT 2:

 postgres=# show work_mem ;
 work_mem
----------
 5MB
(1 row)

postgres=# show maintenance_work_mem;
 maintenance_work_mem
----------------------
 16MB

Answer 1

Building indexes takes a long time, that's normal.

If you are not bottlenecked on I/O, you are probably on CPU.

There are a few things to improve the performance:

• Set maintenance_work_mem very high.

• Use PostgreSQL v11 or better, where several parallel workers can be used.