How to create some GIN index concurrently in Postgresql

Question

I have a large table: CREATE TABLE hh(h int8[] not null, file int8 not null), and GIN-index over h field (CREATE INDEX ON hh USING gin(h)). Index was created is about 8 minutes, so I decide to split this big table into two or more tables, and create indexes over all this tables.

I assumed that these indexes will be created in parallel and I get about double speedup (slightly less because of indexes and tables places on one disk). I assumed that for two tables I got 4-5 minutes (instead of 10 minutes for one table).

But that did not happen! Both indexes created 8-9 minutes, not 4-5 mins.

I try parallel creation of B-tree of indexes over other my big tables, and yes, PostgreSQL can create such indexes in parallel. So I assumed that works for GIN too.

I have enough RAM for indexes: 32GB RAM. Original table has 1Gb size, and original index 3Gb size. Splitted tables has 0.5Gb size and index 1.3Gb size. maintenance_work_mem = 16Gb, work_mem = 16Gb.

I also try to put second table and index in another tablespace (located in other physical disk), but acceleration was very small (7.5 mins vs 8 mins). Why??

So, how to create GIN-indexes in parallel? Is this possible?

Answer 1

To follow up on my comment, PostgreSQL absolutely can build gin indexes on different tables in parallel to each other. If you don't see a benefit, it is because you don't have enough of some resource. Probably random disk IO capacity, as building large gin indexes can be quite intensive on this.

If using a different table space didn't speed it up, then perhaps the IO bottleneck is in the kernel or FS code, such that it fails to take full advantage of the underlying hardware. One way to more-definitively determine the bottleneck would be to run two separate PostgreSQL servers on the same machine (listening to different ports) and then put one table in each database server, and try to create the indexes simultaneously on that set up.