"David Wilson" <david.t.wilson@xxxxxxxxx> 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! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
