Sample Header Ad - 728x90

How to create some GIN index concurrently in Postgresql

3 votes
1 answer
1365 views
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?
Asked by Dmitry Krylov (31 rep)
Mar 6, 2018, 01:42 PM
Last activity: Mar 9, 2018, 10:54 PM