1

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?

  • Use your OS monitoring tools (top, sar, vmstat, perf, etc) to see where the bottleneck is. – jjanes Mar 7 '18 at 14:56
  • I try to split big table in four tables and use more accurate measurement, so I detect some speed up. So my question is wrong, index created concurrently, but be hampered by disk. – Dmitry Krylov Mar 10 '18 at 6:08
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.

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.