Re: [NOVICE] Building full-text index

"Sean Davis" <sdavis2@mail.nih.gov> writes:
> I am trying to build a full-text index (gin(to_tsvector('english',
> title || abstract))) on about 18 million abstracts and titles from
> medical literature. However, I keep getting out-of-memory errors. (I
> am on a 32Gb linux system with maintenance_work_mem set to 20Gb and
> shared buffers at 4Gb; postgres 8.3beta). Does creation of a
> full-text index require that the entire index fit into memory?

I looked closer at this and discovered that there's an overflow problem in the GIN index build code: with maintenance_work_mem above 8Gb, it miscalculates how much space it's used and never realizes when it's reached the intended limit. So indeed you were seeing it try to create the entire index in memory :-(.

This will be fixed in the next beta, but in the meantime set maintenance_work_mem to something less than 8Gb.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match