Re: Out of memory in create index

"David Wilson" <david(dot)t(dot)wilson(at)gmail(dot)com> writes:

> create index val_datestamp_idx on vals(datestamp) tablespace space2;
>
> About 30 seconds into the query, I get:
> ERROR: out of memory
> DETAIL: Failed on request of size 536870912.
>
> Increasing maintenance_work_mem from 1GB to 2GB changed nothing at
> all- exact same error at exact same time. Watching memory on the
> machine shows the out of memory error happens when the machine is only
> at about 35% user. create index concurrently shows an identical error.

Try *lowering* maintenance_work_mem. That's how much memory you're telling the
index build to use. Evidently your machine doesn't have enough RAM/swap to
handle 1G of temporary sort space. In practice values over a few hundred megs
don't seem to help much anyways. Try 512M or 256M.

Also, a little known fact is that an index build can actually allocate
maintenance_work_mem plus an extra work_mem. So if you have work_mem set
unreasonably high that could be contributing to the problem.

> Actually, while I was writing this, I added an additional column to
> the index and it now appears to be completing (memory has reached
> about the point it had been failing at and is now holding steady, and
> the query has been going for significantly longer than the 30 seconds
> or so it took to error out previously). I sort by both columns at
> times, so the extra column may in fact turn out to be useful, but the
> failure of the single column create index in the face of the other
> successful creates has me confused. Can anyone shed some light on the
> situation?

How much memory the OS allows Postgres to allocate will depend on a lot of
external factors. At a guess you had some other services or queries running at
the same time the first time which reduced the available memory.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!